I've got this script that runs on a SQL Server Express 2008 machine.
/* BUD501T */
declare @ErrMsg nvarchar(4000)
declare @ErrLine nvarchar(100)
declare @currentFY nvarchar(1000)
declare @programName nvarchar(1000)
set @ErrMsg = ''
set @ErrLine = ''
set @programName = 'BUDPROD - GL_Exp.sql'
select @currentFY = value from Budgets.dbo.Config where [key] = 'current_fy';
/* GL EXP */
/*Log*/
EXEC [Budgets].dbo.Log_SP @program = @programName ,@message = 'Starting';
--Status
UPDATE [Budgets].[dbo].[Status]
SET [start] = GETDATE()
WHERE [name] = @programName;
BEGIN TRY
--Delete fy
DELETE FROM [Budgets].[dbo].[GL_Exp] WHERE fiscal_year = @currentFY;
--Insert fy
WITH ledger_detail AS (
SELECT pld.fiscal_year,
pld.accounting_period,
pld.financial_deptid AS DEPTID,
pld.fund_code,
pld.class_fld AS CLASS_CODE,
pld.project_id,
pld.program_code,
CASE
WHEN pld.account IN ('500020','520000','520220','520240') THEN 2
WHEN LEFT(pld.account,1) = '5' THEN 1
WHEN LEFT(pld.account,1) = '6' THEN 3
WHEN LEFT(pld.account,1) = '7' THEN 4
WHEN LEFT(pld.account,1) = '8' THEN 5
ELSE 0
END AS ACCT_GRP_CODE,
pld.budget_amount,
pld.encumbrance_amount,
pld.expenditure_amount,
pld.account AS ACCOUNT_CODE,
CASE
WHEN LEFT(pld.class_fld, 2) ='12' THEN 0
WHEN LEFT(pld.class_fld, 3)='113' THEN 3
WHEN LEFT(pld.class_fld, 3)='112' THEN 14
WHEN LEFT(pld.class_fld, 3)='115' THEN 10
WHEN LEFT(pld.class_fld, 3)='116' THEN 13
WHEN LEFT(pld.class_fld, 3)='117' THEN 12
WHEN LEFT(pld.class_fld, 3)='118' THEN 11
WHEN LEFT(pld.class_fld, 2)='13' THEN 2
WHEN LEFT(pld.class_fld, 2)='14' THEN 3
WHEN LEFT(pld.class_fld, 1)='4' THEN 4
WHEN LEFT(pld.class_fld, 1)='6' THEN 6
ELSE 9
END AS FUND_SOURCE
FROM [DWPROD]..[DISC].[PS_LEDGER_DETAIL] pld
WHERE pld.budget_period = @currentFY
AND pld.accounting_period BETWEEN 1 AND 12
AND pld.fiscal_year = 2010
AND pld.financial_deptid BETWEEN '100' AND '999'
AND pld.account BETWEEN '500000' AND '899999')
INSERT INTO [Budgets].[dbo].GL_Exp (fiscal_year, accounting_period, entity_code, division_code, deptid, fund_code, class_code, project_id, program_code, acct_grp_code, account_code, gl_bud_amt, gl_enc_amt, gl_exp_amt, fund_source)
SELECT
x.FISCAL_YEAR,
x.accounting_period,
y.strdepentity AS ENTITY_CODE,
y.depdiv1 AS DIVISION_CODE,
x.deptid,
x.fund_code,
x.class_code,
x.project_id,
x.program_code,
x.acct_grp_code,
x.account_code AS ACCOUNT_CODE,
SUM(x.budget_amount) AS GL_BUD_AMT,
SUM(x.encumbrance_amount) AS GL_ENC_AMT,
SUM(x.expenditure_amount) AS GL_EXP_AMT,
x.fund_source
FROM ledger_detail x
LEFT JOIN [Budgets].[dbo].[Departments] y ON y.deporg = x.deptid
GROUP BY x.fiscal_year, x.accounting_period, y.strdepentity, y.depdiv1, x.deptid, x.fund_code, x.class_code, x.project_id, x.program_code, x.acct_grp_code, x.ACCOUNT_CODE,x.FUND_SOURCE
ORDER BY x.deptid, x.class_code, x.project_id, x.account_code;
--Fixes
--Set rundate
UPDATE [Budgets].[dbo].[GL_Exp] SET RUNDATE = GETDATE()
WHERE (FISCAL_YEAR=@currentFY);
--Set OB amount NOT WORKING
UPDATE x
SET BU_ORIG_BUD_AMT = ob.OB_AMT
FROM [Budgets].[dbo].[GL_Exp] x INNER JOIN [Budgets].[dbo].[OrigBudSumm_VW] ob on x.FISCAL_YEAR = ob.FISCAL_YEAR AND x.ACCOUNT_CODE = ob.ACCOUNT_CODE AND x.PROJECT_ID = ob.PROJECT_ID
WHERE x.FISCAL_YEAR=@currentFY;
UPDATE [Budgets].[dbo].GL_Exp
SET bu_orig_bud_amt = ISNULL(bu_orig_bud_amt, 0)
WHERE fiscal_year = @currentFY;
--Fix Fringes, changes acct_grp_code on flat rate fringes
UPDATE [Budgets].[dbo].[GL_Exp] SET ACCT_GRP_CODE = '0'
WHERE FISCAL_YEAR= @currentFY AND
DEPTID='890' AND GL_BUD_AMT<0 ;
UPDATE [Budgets].[dbo].[GL_Exp] SET ACCT_GRP_CODE = '0'
WHERE FISCAL_YEAR= @currentFY AND DEPTID='894' ;
UPDATE [Budgets].[dbo].[GL_Exp] SET ACCT_GRP_CODE = '2A'
WHERE FISCAL_YEAR= @currentFY AND acct_grp_code='2' And class_code Like '%05';
UPDATE [Budgets].[dbo].[GL_Exp] SET ACCT_GRP_CODE = '2A'
WHERE FISCAL_YEAR= @currentFY AND acct_grp_code='2' And class_code Like '4%';
UPDATE [Budgets].[dbo].[GL_Exp] SET ACCT_GRP_CODE = '2A'
WHERE FISCAL_YEAR= @currentFY AND acct_grp_code='2' And class_code Like '12%';
--Status
UPDATE [Budgets].[dbo].[Status]
SET [status] = 'Ran', [end] = GETDATE()
WHERE [name] = @programName;
END TRY
BEGIN CATCH
select @errMsg = error_message(), @ErrLine = 'Line: ' + cast(ERROR_LINE() as nvarchar(100));
EXEC [Budgets].dbo.Log_SP @program = @programName,@message = @ErrMsg, @errorlevel = 'Error', @location = @ErrLine;
UPDATE [Budgets].[dbo].[Status]
SET [status] = 'Error',
[end] = GETDATE(),
[error_message] = @ErrLine + ' - ' + @ErrMsg
WHERE [name] = @programName;
END CATCH;
/*Log*/
EXEC [Budgets].dbo.Log_SP @program = @programName,@message = 'Finished';
It's long but pretty simple. It clears out a table and inserts updated information, then it does some fixes on the data for our use. It inserts about 200,000 rows of data in the main insert.
My problem is that sometimes this thing will run in about 6 minutes and other times it will run in about 20 min and sometimes it will run in about 2 hrs. I can't peg it down and I've let it run for about a week (it's scheduled to run daily at 5:15 am). Now it does hit some linked servers but I have other jobs that hit the same linked servers and they are consitant, unlike this.
So my question is what's going on? Is it running out of memory (this is the Express version after all)? Can I tell if the linked servers are slowing it down? Is there something wrong with the query?
Thanks, Clint