January 28, 2013 at 9:27 am
I'm getting this error message on a query that I've put together. It queries Infor's Smartstream product for general ledger information. The query was running very slowly the way we originally ran it, so I've tried to speed it up by changing it to be a dynamic query as shown below. The query runs great until you use all parameters. Any idea how to correct this issue?
NOTE: I realize there are a million ways this could've been written differently or better. It gives the results needed and the programmer I had write it back then was just learning SQL and at that point. We needed the report out amongst a dozen other reports we were working on after our transition to Smarstream so I couldn't spend a lot of time correcting. Now that the GL is full of multiple years worth of data the query is starting to crawl.
/****** Object: StoredProcedure [dbo].[spGetGLbyAccount] Script Date: 07/10/2009 11:09:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- MFA Application Development Created Stored Procedure
-- Author:James Stewart
-- Create date: 1/12/2009
-- Description:GL by Account. Returns transactions by year
-- and period. If a transaction has no activity the beginning and
-- ending balance is still reported. We create a temporary table.
-- on the first pass the beginning balance is inserted. On the
-- second pass the detail records are inserted.
-- Dependancies:
--Databases:
--DBSglep, DBSjepc
--Tables:
--dbo.ldr_acct_bal, dbo.posted_jrnl_line, dbo.ldr_acct, dbo.ldr_entity_policy
--Views:
--
--Functions:
--
--
-- ====================================================
-- Consumers
--Reports: General Ledger by Account
--
--Application \ Form:
--
-- ====================================================
-- Modifications:
-- Date: /Developer: / Mod:
-- 4/21/2009 James S. changed ldr_entity_id to line_ldr_entity_id
-- 6/8/2009 James s. Added Macro4 and year function
-- 7/8/2009 James S. Added end of month date
-- 1-25-2013 Brett Phipps. Refactored queries to make the sql dynamic to improve performance
-- ====================================================
ALTER PROCEDURE [dbo].[spGetGLbyAccount]
@Date smallint,
@per tinyint,
@entity char(200) = null,
@account char(500) = null,
@div char(200) = null
AS
BEGIN
SET NOCOUNT ON;
If @div = ' '
begin
set @div = NULL
end
If @entity = ' '
begin
set @entity = NULL
end
If @account = ' '
begin
set @account = NULL
end
-- Get end of month date
-- If period is greater than 4 we subtract 4 from the period to get the
-- month else we add 8 to get the month. I have set the day to 20 because
-- each month has 20 days. I then use the EOM function to get the last day
-- of the month.
Declare @asOfDate as DATETIME
,@sql VARCHAR(max)
If @per > 4
begin
set @asOfDate = cast((@per -4) as varchar(2)) + '/20/' + cast(@Date as varchar(4))
set @asOfDate = dbo.EOMDate(@asOfDate)
end
else
begin
set @asOfDate = cast((@per +8) as varchar(2)) + '/20/' + cast((@Date -1) as varchar(4))
set @asOfDate = dbo.EOMDate(@asOfDate)
end
CREATE TABLE dbo.#balanceAcct
(
mfa_division char(3) null,
mfa_account char(5) null,
mfa_center char(4) null,
entity_descp char(35) null,
acct_descp_1 char(35) null,
acct_descp_2 char(35) null,
eff_date datetime null,
user_alpha_fld_1 varchar(10) null,
user_alpha_fld_2 varchar(15) null,
user_alpha_fld_3 varchar(20) null,
trans_amt money null,
prim_dr_cr_code char(1) null,
descp varchar(80) null,
jrnl_id char(20) null,
Bal money null,
f_year char (4) null,
f_per varchar(2) null,
entity char(5) null,
end_of_month_date datetime null
)
INSERT INTO dbo.#balanceAcct (
mfa_division,
mfa_account,
mfa_center,
Bal,
acct_descp_1,
acct_descp_2,
f_year,
f_per,
entity,
end_of_month_date)
-- Insert statements for procedure here
-- Get beginning balance
SELECT
LAB.mfa_division,
LAB.mfa_account,
LAB.mfa_center,
CASE
WHEN @per = 1 THEN LAB.ldr_amt_0
WHEN @per = 2 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
WHEN @per = 3 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2
WHEN @per = 4 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3
WHEN @per = 5 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4
WHEN @per = 6 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5
WHEN @per = 7 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6
WHEN @per = 8 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7
WHEN @per = 9 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8
WHEN @per = 10 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9
WHEN @per = 11 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9 + LAB.ldr_amt_10
WHEN @per = 12 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9 + LAB.ldr_amt_10 + LAB.ldr_amt_11
ELSE 0
END AS Bal, LA.acct_descp_1, LA.acct_descp_2
, @Date
, @per
, LAB.ldr_entity_id
, @asOfDate
FROM
DBSglep.dbo.ldr_acct_bal AS LAB
RIGHT OUTER JOIN
DBSglep.dbo.ldr_acct AS LA ON LAB.ldr_entity_id = LA.ldr_entity_id AND LAB.mfa_division = LA.mfa_division AND LAB.mfa_account = LA.mfa_account AND
LAB.mfa_center = LA.mfa_center
WHERE
(LAB.processing_yr = @Date) AND
(LAB.amt_class_type = 'ACTUAL') AND
((LAB.mfa_division <> '999') and (LAB.mfa_division <> '207')) AND
((LAB.mfa_division in
(select par_values
from dbo.fn_parse_multi_params_to_table(@div))
OR @div is NULL))
and ((LAB.mfa_account in
(select par_values
from dbo.fn_parse_multi_params_to_table(@account))
or @account is Null))
and ((LAB.ldr_entity_id in
(select par_values
from dbo.fn_parse_multi_params_to_table(@entity))
or @entity is Null))
AND Not
((@per = 1 and LAB.ldr_amt_0 = 0) or
(@per = 2 and (LAB.ldr_amt_0 + LAB.ldr_amt_1) = 0) or
(@per = 3 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2) = 0) or
(@per = 4 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3) = 0) or
(@per = 5 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4) = 0) or
(@per = 6 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5) = 0) or
(@per = 7 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6) = 0) or
(@per = 8 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7) = 0) or
(@per = 9 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8) = 0) or
(@per = 10 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9) = 0) or
(@per = 11 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9 + LAB.ldr_amt_10) = 0) or
(@per = 12 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9 + LAB.ldr_amt_10 + LAB.ldr_amt_11) = 0))
--Get detail
SET @sql =
'INSERT INTO dbo.#balanceAcct (
mfa_division,
mfa_account,
mfa_center,
entity_descp,
acct_descp_1,
acct_descp_2,
eff_date,
user_alpha_fld_1,
user_alpha_fld_2,
user_alpha_fld_3,
trans_amt,
prim_dr_cr_code,
descp,
jrnl_id,
entity,
end_of_month_date)
SELECT LAB.mfa_division
, LAB.mfa_account
, LAB.mfa_center
, LEP.entity_descp
, LA.acct_descp_1
, LA.acct_descp_2
, CONVERT(varchar, PJL.eff_date, 101) AS Edate
, PJL.jrnl_user_alpha_fld_1
, PJL.jrnl_user_alpha_fld_2
, PJL.jrnl_user_alpha_fld_3
, PJL.trans_amt
, PJL.prim_dr_cr_code
, PJL.descp
, PJL.jrnl_id
, LAB.ldr_entity_id
, ' + CONVERT(VARCHAR(20), @asOfDate, 101) + '
FROM DBSglep.dbo.ldr_acct_bal AS LAB RIGHT OUTER JOIN
DBSjepc.dbo.posted_jrnl_line AS PJL ON LAB.ldr_entity_id = PJL.line_ldr_entity_id
AND LAB.mfa_division = PJL.mfa_division
AND LAB.mfa_account = PJL.mfa_account
AND LAB.mfa_center = PJL.mfa_center
AND LAB.processing_yr = PJL.posting_yr
AND LAB.amt_class_type = PJL.amt_class_1_type
right OUTER JOIN
DBSglep.dbo.ldr_entity_policy AS LEP ON LAB.ldr_entity_id = LEP.ldr_entity_id
RIGHT OUTER JOIN
DBSglep.dbo.ldr_acct AS LA ON LAB.ldr_entity_id = LA.ldr_entity_id
AND LAB.mfa_division = LA.mfa_division
AND LAB.mfa_account = LA.mfa_account
AND LAB.mfa_center = LA.mfa_center
WHERE (LAB.processing_yr = ' + CAST(@Date AS CHAR(4)) + ') AND
(LAB.amt_class_type = ''ACTUAL'') AND
(PJL.posting_pd = ' + CAST(@per AS CHAR(1)) + ') AND
(PJL.posting_yr = LAB.processing_yr) AND
((LAB.mfa_division <> ''999'') and (LAB.mfa_division <> ''207''))
'
IF @divIS NOT NULL
BEGIN
SET @sql = @sql +
'and LAB.mfa_division in
(select par_values
from dbo.fn_parse_multi_params_to_table(''' + @div + '''))'
END
IF @account IS NOT NULL
BEGIN
SET @sql = @sql +
'and LAB.mfa_account in
(select par_values
from dbo.fn_parse_multi_params_to_table(''' + @account + '''))'
END
IF @entity IS NOT NULL
BEGIN
SET @sql = @sql +
'and LAB.ldr_entity_id in
(select par_values
from dbo.fn_parse_multi_params_to_table(''' + @entity + '''))'
END
EXEC (@sql)
SELECT
mfa_division,
mfa_account,
mfa_center,
entity_descp,
acct_descp_1,
acct_descp_2,
eff_date,
user_alpha_fld_1,
user_alpha_fld_2,
user_alpha_fld_3,
trans_amt,
prim_dr_cr_code,
descp,
jrnl_id,
Bal,
f_year,
f_per,
entity,
end_of_month_date
FROM dbo.#balanceAcct
order by entity
, mfa_account
, mfa_division
, mfa_center
, user_alpha_fld_1
DROP TABLE dbo.#balanceAcct
-- ====================================================
-- Example to execute the stored procedure
-- ====================================================
/*
EXECUTE dbo.spGetGLbyAccount '2009', '10', '70100'
*/
END
January 28, 2013 at 9:36 am
Have you actually found which particular query in your stored proc is slow? Is the one which uses dynamic sql?
Can you please post DDL of fn_parse_multi_params_to_table?
January 28, 2013 at 9:42 am
We use this function on several of reports: It's a vb app. It's super duper fast, so I know it's not the issue.
I only have a problem when I pass in every possible parameter. Otherwise, the queries tend to run in under 10 seconds.
USE [database]
GO
/****** Object: UserDefinedFunction [dbo].[fn_parse_multi_params_to_table] Script Date: 01/28/2013 10:40:49 ******/
CREATE FUNCTION [dbo].[fn_parse_multi_params_to_table](@RawParameter [nvarchar](4000))
RETURNS TABLE (
[par_values] [nvarchar](max) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ParseParamList].[ParseParamList.UserDefinedFunctions].[fn_ParseMultiParametersToTable]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_parse_multi_params_to_table'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'fn_ParseMultiParametersToTable.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_parse_multi_params_to_table'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=15 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_parse_multi_params_to_table'
GO
January 28, 2013 at 10:01 am
Try to get generated sql string out of stored proc, then execute it and post the query plan. Wihtout seeing it it's hard to guess what could be wrong with your query.
Also, your CLR function defines the input parameter as nvarchar(4000), but output the table with nvarchar(max)... I would recommend to change it to nvarchar(4000) too.
Wait, your output table defines par_values as nullable. Sometimes check using "IN" against list which might contain NULL values are quite slow. Try to replace it with EXISTS:
...
IF @divIS NOT NULL
BEGIN
SET @sql = @sql +
'and EXISTS (select 1 from dbo.fn_parse_multi_params_to_table(''' + @div +
''') where par_values=LAB.mfa_division)'
END
...
January 28, 2013 at 10:16 am
UGH! Went to test Eugene's suggestion and now the server is giving me the following problem:
Msg 6513, Level 16, State 27, Procedure spGetGLbyAccount, Line 99
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration features.
I'm only getting this error in our test environment. Our "DBA" solved the problem in test last week by restarting the server. But now it's back again.
January 28, 2013 at 10:21 am
Don't blame me :hehe:
January 28, 2013 at 10:40 am
Heh. Nah. I know it's not you. I've already looked at the link you graciously provided.
We had this problem a while ago in production and after a long time they finally got it fixed. But now our test environment is having the same issue, and he apparently can't recall what he did to fix it.
I wonder if his fix isn't to restart the production server every night at 4 am....
January 29, 2013 at 1:59 am
...
I wonder if his fix isn't to restart the production server every night at 4 am....
I would call it anything but fix;-)
January 29, 2013 at 3:50 am
While yes, there are a million things I'd do to tune that query, it's not so complex that you should be seeing memory issues. I'd say you may want to focus there first. It sounds as if you may have a bug. Check the server's version and service pack level first. See if you just need to update. Then I'd look at memory allocations and see what the heck is chewing up all the resources. Take a look at sys.dm_exec_query_memory_grants to see if queries are chewing on memory. How much memory does the system have?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2013 at 6:28 am
It's SS 2005. 32 bit server with 4gig of ram. He changed the settings to only allow it to use 2 gig of ram. This seems to have resolved the issue. I was getting a weird. Syntax error yesterday that I haven't resolved yet.
January 29, 2013 at 6:38 am
Brett Phipps (1/29/2013)
It's SS 2005. 32 bit server with 4gig of ram. He changed the settings to only allow it to use 2 gig of ram. This seems to have resolved the issue. I was getting a weird. Syntax error yesterday that I haven't resolved yet.
Whoa! 2gb of ram is a VERY small system. Setting the memory size to fixed is the right way to go though.
I'd still check the service packs & CUs to be sure you're up to date.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2013 at 10:24 am
Well, I've finally worked my way through some syntax issues, etc and instituted the changes that Eugene suggested. But unfortunately, it did not solve the issue. In fact, I really can't see a major performance difference between the two methods.
I'm still getting the error shown below if I execute the stored procedure with all possible parameters. Anybody got any other suggestions, short of telling the users to only use 2 out of the 3 parameters?
Msg 8623, Level 16, State 1, Line 1
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
Here's the code in the latest version
ALTER PROCEDURE [dbo].[spGetGLbyAccount]
@Date SMALLINT
, @per TINYINT
, @entity CHAR(200) = NULL
, @account CHAR(500) = NULL
, @div CHAR(200) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @div = ' '
BEGIN
SET @div = NULL
END
IF @entity = ' '
BEGIN
SET @entity = NULL
END
IF @account = ' '
BEGIN
SET @account = NULL
END
-- Get end of month date
-- If period is greater than 4 we subtract 4 from the period to get the
-- month else we add 8 to get the month. I have set the day to 20 because
-- each month has 20 days. I then use the EOM function to get the last day
-- of the month.
DECLARE @asOfDate AS DATETIME
, @sql NVARCHAR(4000)
IF @per > 4
BEGIN
SET @asOfDate = CAST((@per - 4) AS VARCHAR(2)) + '/20/'
+ CAST(@Date AS VARCHAR(4))
SET @asOfDate = dbo.EOMDate(@asOfDate)
END
ELSE
BEGIN
SET @asOfDate = CAST((@per + 8) AS VARCHAR(2)) + '/20/'
+ CAST((@Date - 1) AS VARCHAR(4))
SET @asOfDate = dbo.EOMDate(@asOfDate)
END
CREATE TABLE dbo.#balanceAcct (mfa_division CHAR(3) NULL
, mfa_account CHAR(5) NULL
, mfa_center CHAR(4) NULL
, entity_descp CHAR(35) NULL
, acct_descp_1 CHAR(35) NULL
, acct_descp_2 CHAR(35) NULL
, eff_date DATETIME NULL
, user_alpha_fld_1 VARCHAR(10) NULL
, user_alpha_fld_2 VARCHAR(15) NULL
, user_alpha_fld_3 VARCHAR(20) NULL
, trans_amt MONEY NULL
, prim_dr_cr_code CHAR(1) NULL
, descp VARCHAR(80) NULL
, jrnl_id CHAR(20) NULL
, Bal MONEY NULL
, f_year CHAR(4) NULL
, f_per VARCHAR(2) NULL
, entity CHAR(5) NULL
, end_of_month_date DATETIME NULL)
INSERT INTO dbo.#balanceAcct
(mfa_division
, mfa_account
, mfa_center
, Bal
, acct_descp_1
, acct_descp_2
, f_year
, f_per
, entity
, end_of_month_date
)
-- Insert statements for procedure here
-- Get beginning balance
SELECT LAB.mfa_division
, LAB.mfa_account
, LAB.mfa_center
, CASE WHEN @per = 1 THEN LAB.ldr_amt_0
WHEN @per = 2
THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
WHEN @per = 3
THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
+ LAB.ldr_amt_2
WHEN @per = 4
THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
+ LAB.ldr_amt_2 + LAB.ldr_amt_3
WHEN @per = 5
THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
+ LAB.ldr_amt_2 + LAB.ldr_amt_3
+ LAB.ldr_amt_4
WHEN @per = 6
THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
+ LAB.ldr_amt_2 + LAB.ldr_amt_3
+ LAB.ldr_amt_4 + LAB.ldr_amt_5
WHEN @per = 7
THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
+ LAB.ldr_amt_2 + LAB.ldr_amt_3
+ LAB.ldr_amt_4 + LAB.ldr_amt_5
+ LAB.ldr_amt_6
WHEN @per = 8
THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
+ LAB.ldr_amt_2 + LAB.ldr_amt_3
+ LAB.ldr_amt_4 + LAB.ldr_amt_5
+ LAB.ldr_amt_6 + LAB.ldr_amt_7
WHEN @per = 9
THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
+ LAB.ldr_amt_2 + LAB.ldr_amt_3
+ LAB.ldr_amt_4 + LAB.ldr_amt_5
+ LAB.ldr_amt_6 + LAB.ldr_amt_7
+ LAB.ldr_amt_8
WHEN @per = 10
THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
+ LAB.ldr_amt_2 + LAB.ldr_amt_3
+ LAB.ldr_amt_4 + LAB.ldr_amt_5
+ LAB.ldr_amt_6 + LAB.ldr_amt_7
+ LAB.ldr_amt_8 + LAB.ldr_amt_9
WHEN @per = 11
THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
+ LAB.ldr_amt_2 + LAB.ldr_amt_3
+ LAB.ldr_amt_4 + LAB.ldr_amt_5
+ LAB.ldr_amt_6 + LAB.ldr_amt_7
+ LAB.ldr_amt_8 + LAB.ldr_amt_9
+ LAB.ldr_amt_10
WHEN @per = 12
THEN LAB.ldr_amt_0 + LAB.ldr_amt_1
+ LAB.ldr_amt_2 + LAB.ldr_amt_3
+ LAB.ldr_amt_4 + LAB.ldr_amt_5
+ LAB.ldr_amt_6 + LAB.ldr_amt_7
+ LAB.ldr_amt_8 + LAB.ldr_amt_9
+ LAB.ldr_amt_10 + LAB.ldr_amt_11
ELSE 0
END AS Bal
, LA.acct_descp_1
, LA.acct_descp_2
, @Date
, @per
, LAB.ldr_entity_id
, @asOfDate
FROM DBSglep.dbo.ldr_acct_bal AS LAB
RIGHT OUTER JOIN DBSglep.dbo.ldr_acct AS LA
ON LAB.ldr_entity_id = LA.ldr_entity_id
AND LAB.mfa_division = LA.mfa_division
AND LAB.mfa_account = LA.mfa_account
AND LAB.mfa_center = LA.mfa_center
WHERE (LAB.processing_yr = @Date)
AND (LAB.amt_class_type = 'ACTUAL')
AND ((LAB.mfa_division <> '999')
AND (LAB.mfa_division <> '207')
)
AND (( LAB.mfa_division IN (SELECT par_values
FROM dbo.fn_parse_multi_params_to_table(@div))
OR @div IS NULL)
)
AND (( LAB.mfa_account IN (SELECT par_values
FROM dbo.fn_parse_multi_params_to_table(@account))
OR @account IS NULL)
)
AND (( LAB.ldr_entity_id IN (SELECT par_values
FROM dbo.fn_parse_multi_params_to_table(@entity))
OR @entity IS NULL)
)
AND NOT ((@per = 1 AND LAB.ldr_amt_0 = 0)
OR (@per = 2 AND (LAB.ldr_amt_0 + LAB.ldr_amt_1) = 0)
OR (@per = 3 AND (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2) = 0)
OR (@per = 4 AND (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3) = 0)
OR (@per = 5 AND (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4) = 0)
OR (@per = 6 AND (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5) = 0)
OR (@per = 7 AND (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6) = 0)
OR (@per = 8 AND (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7) = 0)
OR (@per = 9 AND (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8) = 0)
OR (@per = 10 AND (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9) = 0)
OR (@per = 11 AND (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9 + LAB.ldr_amt_10) = 0)
OR (@per = 12 AND (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9 + LAB.ldr_amt_10 + LAB.ldr_amt_11) = 0))
--Get detail
SET @sql = 'INSERT INTO dbo.#balanceAcct (
mfa_division,
mfa_account,
mfa_center,
entity_descp,
acct_descp_1,
acct_descp_2,
eff_date,
user_alpha_fld_1,
user_alpha_fld_2,
user_alpha_fld_3,
trans_amt,
prim_dr_cr_code,
descp,
jrnl_id,
entity,
end_of_month_date)
SELECT LAB.mfa_division
, LAB.mfa_account
, LAB.mfa_center
, LEP.entity_descp
, LA.acct_descp_1
, LA.acct_descp_2
, CONVERT(varchar, PJL.eff_date, 101) AS Edate
, PJL.jrnl_user_alpha_fld_1
, PJL.jrnl_user_alpha_fld_2
, PJL.jrnl_user_alpha_fld_3
, PJL.trans_amt
, PJL.prim_dr_cr_code
, PJL.descp
, PJL.jrnl_id
, LAB.ldr_entity_id
, ' + CONVERT(VARCHAR(20), @asOfDate, 101) + '
FROMDBSglep.dbo.ldr_acct_bal AS LAB
RIGHT OUTER JOINDBSjepc.dbo.posted_jrnl_line AS PJL
ON LAB.ldr_entity_id = PJL.line_ldr_entity_id
AND LAB.mfa_division = PJL.mfa_division
AND LAB.mfa_account = PJL.mfa_account
AND LAB.mfa_center = PJL.mfa_center
AND LAB.processing_yr = PJL.posting_yr
AND LAB.amt_class_type = PJL.amt_class_1_type
right OUTER JOINDBSglep.dbo.ldr_entity_policy AS LEP
ON LAB.ldr_entity_id = LEP.ldr_entity_id
RIGHT OUTER JOINDBSglep.dbo.ldr_acct AS LA
ON LAB.ldr_entity_id = LA.ldr_entity_id
AND LAB.mfa_division = LA.mfa_division
AND LAB.mfa_account = LA.mfa_account
AND LAB.mfa_center = LA.mfa_center
WHERE (LAB.processing_yr = @Date )
AND (LAB.amt_class_type = ''ACTUAL'')
AND (PJL.posting_pd = @per )
AND (PJL.posting_yr = LAB.processing_yr)
AND ((LAB.mfa_division <> ''999'') and (LAB.mfa_division <> ''207''))
'
IF @div IS NOT NULL
BEGIN
SET @sql = @sql
+ --' and LAB.mfa_division in
--(select par_values
--from dbo.fn_parse_multi_params_to_table(''' + @div + '''))'
'and EXISTS (select 1 from dbo.fn_parse_multi_params_to_table('''
+ @div + ''') where par_values=LAB.mfa_division)'
END
IF @account IS NOT NULL
BEGIN
SET @sql = @sql
+ --' and LAB.mfa_account in
--(select par_values
--from dbo.fn_parse_multi_params_to_table(''' + @account + '''))'
'and EXISTS (select 1 from dbo.fn_parse_multi_params_to_table('''
+ @account
+ ''') where par_values = LAB.mfa_account)'
END
IF @entity IS NOT NULL
BEGIN
SET @sql = @sql
+ --' and LAB.ldr_entity_id in
--(select par_values
--from dbo.fn_parse_multi_params_to_table(''' + @entity + '''))'
'and EXISTS (select 1 from dbo.fn_parse_multi_params_to_table('''
+ @entity
+ ''') where par_values = LAB.ldr_entity_id)'
END
EXEC sys.sp_executesql @sql,
N'@per tinyint output, @Date smallint output', @per OUTPUT,
@Date OUTPUT
SELECT mfa_division
, mfa_account
, mfa_center
, entity_descp
, acct_descp_1
, acct_descp_2
, eff_date
, user_alpha_fld_1
, user_alpha_fld_2
, user_alpha_fld_3
, trans_amt
, prim_dr_cr_code
, descp
, jrnl_id
, Bal
, f_year
, f_per
, entity
, end_of_month_date
FROM dbo.#balanceAcct
ORDER BY entity
, mfa_account
, mfa_division
, mfa_center
, user_alpha_fld_1
DROP TABLE dbo.#balanceAcct
END
go
EXECUTE dbo.spGetGLbyAccount '2010', '10', '10100', '60730', '103'
January 29, 2013 at 10:27 am
Grant Fritchey (1/29/2013)
Brett Phipps (1/29/2013)
It's SS 2005. 32 bit server with 4gig of ram. He changed the settings to only allow it to use 2 gig of ram. This seems to have resolved the issue. I was getting a weird. Syntax error yesterday that I haven't resolved yet.Whoa! 2gb of ram is a VERY small system. Setting the memory size to fixed is the right way to go though.
I'd still check the service packs & CUs to be sure you're up to date.
Ran select @@version
Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86) Mar 25 2011 13:50:04 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
But I'm in development and have no control over what is applied to the server itself, so I can't do much more than look at this.
January 29, 2013 at 11:34 am
You are two service packs behind.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2013 at 3:29 pm
Grant Fritchey (1/29/2013)
You are two service packs behind.
That's the windows operating system. Not sql server.
SELECT @@version
Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86) Mar 25 2011 13:50:04 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
9.00.5057.00SP4Standard Edition
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply