March 24, 2009 at 12:36 pm
We created this stored procedure that I copied below, that is real slow on our SQL 2000 box which is build 8.00.2050. It takes 6 minutes to run and if I execute each statement individually it take 2 secs. If I exec the same stored procedure on a SQL 2005 instance it only take 2 sec.
Are there any issue on SQL 2000 related to slow stored procedures?
Wondering if someone can clue me in to what I might try or look at to get the stored procedure running a little faster.
Below is the procedure code
USE [SSIS_Common]
GO
/****** Object: StoredProcedure [dbo].[proc_Update_PA_Audit_Salary] Script Date: 03/23/2009 10:38:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_Update_PA_Audit_Salary]
AS
set nocount on
IF EXISTS (SELECT 1
FROM sysobjects
WHERE xtype='u' AND name='tbl_PA_Audit_Salary')
drop TABLE dbo.tbl_PA_Audit_Salary
select distinct employee,acct_unit,account,
sum(amt) as amt,date_gl, last_name,first_name,middle_init,check_nbr,
cast(0 as decimal (13,2)) as amt2100,
cast(0 as decimal (13,2)) as amt2200,
cast(0 as decimal (13,2)) as amt2202,
cast(0 as decimal (13,2)) as amt2300,
cast(0 as decimal (13,2)) as amt2310,
cast(0 as decimal (13,2)) as amt2400,
cast(0 as decimal (13,2)) as amt2750,
2 as digit
into dbo.tbl_PA_Audit_Salary
from dbo.tbl_pa_audit_fin_details
where
substring(account,3,1) = '1'
group by
employee,acct_unit,account,
date_gl, last_name,first_name,middle_init,check_nbr
--select * from dbo.tbl_PA_Audit_Salary
IF EXISTS (SELECT 1
FROM sysobjects
WHERE xtype='u' AND name='tbl_PA_Audit_Salary_Work')
drop table dbo.tbl_PA_Audit_Salary_Work
select distinct employee, acct_unit,min(account) as account, check_nbr
into dbo.tbl_PA_Audit_Salary_Work
from dbo.tbl_PA_Audit_Salary
group by employee,acct_unit, check_nbr
order by employee,acct_unit, check_nbr
update dbo.tbl_PA_Audit_Salary
set digit = (select 1
from dbo.tbl_PA_Audit_Salary_Work
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_PA_Audit_Salary_Work.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_PA_Audit_Salary_Work.acct_unit and
dbo.tbl_PA_Audit_Salary.account = dbo.tbl_PA_Audit_Salary_Work.account and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_PA_Audit_Salary_Work.check_nbr )
where exists
(select *
from dbo.tbl_PA_Audit_Salary_Work
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_PA_Audit_Salary_Work.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_PA_Audit_Salary_Work.acct_unit and
dbo.tbl_PA_Audit_Salary.account = dbo.tbl_PA_Audit_Salary_Work.account and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_PA_Audit_Salary_Work.check_nbr )
update dbo.tbl_PA_Audit_Salary
set amt2100 = (select sum(amt)
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2100 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
where exists
(select *
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2100 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
update dbo.tbl_PA_Audit_Salary
set amt2200 = (select sum(amt)
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2200 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
where exists
(select *
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2200 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
update dbo.tbl_PA_Audit_Salary
set amt2202 = (select sum(amt)
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2202 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
where exists
(select *
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2202 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
update dbo.tbl_PA_Audit_Salary
set amt2300 = (select sum(amt)
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2300 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
where exists
(select *
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2300 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
update dbo.tbl_PA_Audit_Salary
set amt2310 = (select sum(amt)
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2310 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
where exists
(select *
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2310 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
update dbo.tbl_PA_Audit_Salary
set amt2400 = (select sum(amt)
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2400 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
where exists
(select *
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2400 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
update dbo.tbl_PA_Audit_Salary
set amt2750 = (select sum(amt)
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2750 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
where exists
(select *
from dbo.tbl_pa_audit_fin_details
where dbo.tbl_PA_Audit_Salary.employee = dbo.tbl_pa_audit_fin_details.employee and
dbo.tbl_PA_Audit_Salary.acct_unit = dbo.tbl_pa_audit_fin_details.acct_unit and
dbo.tbl_pa_audit_fin_details.account = 2750 and
dbo.tbl_PA_Audit_Salary.digit=1 and
dbo.tbl_PA_Audit_Salary.check_nbr = dbo.tbl_pa_audit_fin_details.check_nbr )
grant select on dbo.tbl_PA_Audit_Salary to crystal
March 24, 2009 at 12:39 pm
shouldn't be, it's a normal correlated query
update stats and indexes to make sure those are not the causes
and of course, get an Execution Plan in 2000 and 2005 to compare
March 24, 2009 at 12:44 pm
Thanks, I looked at both execution plans and they both appear the same. I'll try updating stats and see.
Update:
I updated the stats and still the same thing. On our Test SQL 2000 box it takes 12 min compared to a few seconds running individual statements.
March 24, 2009 at 1:37 pm
Another update:
If I highlight all the SQL statements in the stored procedure and execute it, it takes the same amount of time as executing the stored procedure. But if I only highlight one statement at a time and execute, it will execute as fast as I highlight the statements.
March 24, 2009 at 2:04 pm
On something this complex, I would move each set of updates to it's own proc, and create one proc that calls each of those in turn. Try that, see if it speeds it up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply