February 25, 2009 at 10:37 am
Can someone see what else can be done to optimize this script? It is taking almost 2 minute to return one row.
select b.homelaborlevelname1 as company
,b.homelaborleveldsc1 as company_descr
,b.homelaborleveldsc3 as location
,b.personnum as emplid
,b.personfullname as employee
,e.personfullname as prev_approver
,d.enteredondtm as prev_approval_time
,c.personfullname as last_approver
,a.enteredondtm as last_approval_time
from vp_person b
inner join vp_wfcaudit a on a.personnum = b.personnum
and a.enteredondtm = (select max(enteredondtm) from vp_wfcaudit -- get max Supervisor Approval row within past 2 weeks
where personfullname = a.personfullname
and personnum = a.personnum
and wfcaudittype = a.wfcaudittype
and clientusername <> b.personnum -- exclude where employee is approver
and enteredondtm between dateadd(d,-14,getdate()) and getdate())
and a.wfcaudittype = 'Supervisor approval'
inner join vp_person c on a.clientusername = c.personnum -- get last approver name
inner join vp_wfcaudit d on d.personnum = b.personnum
and d.enteredondtm = (select max(enteredondtm) from vp_wfcaudit -- get max Supervisor Approval row prior to above row
where personfullname = d.personfullname
and personnum = d.personnum
and wfcaudittype = d.wfcaudittype
and clientusername <> b.personnum
and enteredondtm < a.enteredondtm)
and d.wfcaudittype = 'Supervisor approval'
inner join vp_person e on d.clientusername = e.personnum -- get previous approver name
where b.employmentstatus = 'active'
and b.homelaborlevelname1 not in ('Test01','uis','dlx','art','rtl','lfs','mcm','rad')
and b.personnum = '00005880'
and e.personnum <> c.personnum -- only include where previous approver is different than current approver
order by company, employee
February 25, 2009 at 11:16 am
Can you post the execution plan? Save it, zip it, upload it.
- 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
February 25, 2009 at 11:20 am
Since it's 2000, to get the exec plan, run the query with the statement SET STATISTICS PROFILE ON above it.
There will be a second resultset. Copy that, paste in excel, save, zip and attach.
Also, indexes and table definition please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2009 at 11:32 am
how do you save the execution plan? its graphical
February 25, 2009 at 11:52 am
attach is the result from statistics profile
February 25, 2009 at 11:57 am
(select max(enteredondtm) from vp_wfcaudit -- get max Supervisor Approval row within past 2 weeks
where personfullname = a.personfullname
and personnum = a.personnum
and wfcaudittype = a.wfcaudittype
and clientusername <> b.personnum -- exclude where employee is approver
and enteredondtm between dateadd(d,-14,getdate()) and getdate())
(select max(enteredondtm) from vp_wfcaudit -- get max Supervisor Approval row prior to above row
where personfullname = d.personfullname
and personnum = d.personnum
and wfcaudittype = d.wfcaudittype
and clientusername <> b.personnum
and enteredondtm < a.enteredondtm)
Would it help or hurt to turn these subqueries into derived tables and put the inequalities in the WHERE clause?
Just a thought.
February 25, 2009 at 12:51 pm
Correlated sub-queries in the join criteria was my first thought for the cause of the slowness, but I wanted to see the execution plan to see what SQL Server did with them. Sometimes it does clever things with those.
- 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
February 25, 2009 at 1:02 pm
Someone want to explain what the statistics profile is saying? Looks like Greek to me, and i need the English translation. This is where I would be trying something different using my gut to help find the right solution.
Which is why I need to learn Greek, giving me another fishing pole ...
February 25, 2009 at 1:38 pm
Lynn Pettis (2/25/2009)
Someone want to explain what the statistics profile is saying?
Will look in the morning, providing Grant doesn't get it first.
Is 11pm, and I'm dead tired.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2009 at 1:38 pm
I see a fair number of bookmark lookups in the execution plan. You might want to look into what you have for indexes on this query. Covering indexes might help.
Also, try this, see if it gets you what you need and if it speeds it up at all:
select b.homelaborlevelname1 as company
,b.homelaborleveldsc1 as company_descr
,b.homelaborleveldsc3 as location
,b.personnum as emplid
,b.personfullname as employee
,e.personfullname as prev_approver
,d.enteredondtm as prev_approval_time
,c.personfullname as last_approver
,a.enteredondtm as last_approval_time
from vp_person b
inner join
(select -- get max Supervisor Approval row within past 2 weeks
personfullname,
personnum,
wfcaudittype,
clientusername,
max(enteredondtm) as lastaudit
from
vp_wfcaudit
where
enteredondtm between dateadd(d,-14,getdate()) and getdate()
and
a.wfcaudittype = 'Supervisor approval'
group by
personfullname,
personnum,
wfcaudittype,
clientusername) Sub1
on b.personfullname = sub1.personfullname
and b.personnum = sub1.personnum
and b.personnum != sub1.clientusername
inner join
vp_wfcaudit a
on a.personnum = b.personnum
and a.enteredondtm = sub1.lastaudit
and a.wfcaudittype = 'Supervisor approval'
inner join
vp_person c
on a.clientusername = c.personnum -- get last approver name
inner join
vp_wfcaudit d
on d.personnum = b.personnum
and d.enteredondtm =
(select max(enteredondtm) from vp_wfcaudit -- get max Supervisor Approval row prior to above row
where personfullname = d.personfullname
and personnum = d.personnum
and wfcaudittype = d.wfcaudittype
and clientusername <> b.personnum
and enteredondtm < a.enteredondtm)
and d.wfcaudittype = 'Supervisor approval'
inner join vp_person e on d.clientusername = e.personnum -- get previous approver name
where b.employmentstatus = 'active'
and b.homelaborlevelname1 not in ('Test01','uis','dlx','art','rtl','lfs','mcm','rad')
and b.personnum = '00005880'
and e.personnum <> c.personnum -- only include where previous approver is different than current approver
order by company, employee;
- 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
February 25, 2009 at 2:35 pm
GSquared (2/25/2009)
I see a fair number of bookmark lookups in the execution plan. You might want to look into what you have for indexes on this query. Covering indexes might help.Also, try this, see if it gets you what you need and if it speeds it up at all:
select b.homelaborlevelname1 as company
,b.homelaborleveldsc1 as company_descr
,b.homelaborleveldsc3 as location
,b.personnum as emplid
,b.personfullname as employee
,e.personfullname as prev_approver
,d.enteredondtm as prev_approval_time
,c.personfullname as last_approver
,a.enteredondtm as last_approval_time
from vp_person b
inner join
(select -- get max Supervisor Approval row within past 2 weeks
personfullname,
personnum,
wfcaudittype,
clientusername,
max(enteredondtm) as lastaudit
from
vp_wfcaudit
where
enteredondtm between dateadd(d,-14,getdate()) and getdate()
and
a.wfcaudittype = 'Supervisor approval'
group by
personfullname,
personnum,
wfcaudittype,
clientusername) Sub1
on b.personfullname = sub1.personfullname
and b.personnum = sub1.personnum
and b.personnum != sub1.clientusername
inner join
vp_wfcaudit a
on a.personnum = b.personnum
and a.enteredondtm = sub1.lastaudit
and a.wfcaudittype = 'Supervisor approval'
inner join
vp_person c
on a.clientusername = c.personnum -- get last approver name
inner join
vp_wfcaudit d
on d.personnum = b.personnum
and d.enteredondtm =
(select max(enteredondtm) from vp_wfcaudit -- get max Supervisor Approval row prior to above row
where personfullname = d.personfullname
and personnum = d.personnum
and wfcaudittype = d.wfcaudittype
and clientusername <> b.personnum
and enteredondtm < a.enteredondtm)
and d.wfcaudittype = 'Supervisor approval'
inner join vp_person e on d.clientusername = e.personnum -- get previous approver name
where b.employmentstatus = 'active'
and b.homelaborlevelname1 not in ('Test01','uis','dlx','art','rtl','lfs','mcm','rad')
and b.personnum = '00005880'
and e.personnum <> c.personnum -- only include where previous approver is different than current approver
order by company, employee;
This does increase performance tremendously (3 sec runtime), however, this part returns multiple max rows if enteredondtm is within pass 2 weeks. I only want to return the single max row.
inner join
(select -- get max Supervisor Approval row within past 2 weeks
personfullname,
personnum,
wfcaudittype,
clientusername,
max(enteredondtm) as enteredondtm
from
vp_wfcaudit
where
enteredondtm between dateadd(d,-14,getdate()) and getdate()
and
wfcaudittype = 'Supervisor approval'
group by
personfullname,
personnum,
wfcaudittype,
clientusername) Sub1
on b.personfullname = sub1.personfullname
and b.personnum = sub1.personnum
and b.personnum != sub1.clientusername
February 25, 2009 at 2:53 pm
Try changing that part to this, see if it gets you what you need:
inner join
(select -- get max Supervisor Approval row within past 2 weeks
personfullname,
personnum,
wfcaudittype,
max(enteredondtm) as lastaudit
from
vp_wfcaudit
where
enteredondtm between dateadd(d,-14,getdate()) and getdate()
and
a.wfcaudittype = 'Supervisor approval'
and
personnum != clientusername
group by
personfullname,
personnum,
wfcaudittype) Sub1
on b.personfullname = sub1.personfullname
and b.personnum = sub1.personnum
- 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
February 25, 2009 at 3:21 pm
GSquared (2/25/2009)
Try changing that part to this, see if it gets you what you need:
inner join(select -- get max Supervisor Approval row within past 2 weeks
personfullname,
personnum,
wfcaudittype,
max(enteredondtm) as lastaudit
from
vp_wfcaudit
where
enteredondtm between dateadd(d,-14,getdate()) and getdate()
and
a.wfcaudittype = 'Supervisor approval'
and
personnum != clientusername
group by
personfullname,
personnum,
wfcaudittype) Sub1
on b.personfullname = sub1.personfullname
and b.personnum = sub1.personnum
That wont work because I need the clientusername field in the Select to get the Last Approver Name
February 26, 2009 at 7:33 am
You should still be able to get that from the call to vp_wfcaudit right after that sub-query, where it's alliased as "a".
- 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
February 26, 2009 at 8:32 am
GSquared (2/26/2009)
You should still be able to get that from the call to vp_wfcaudit right after that sub-query, where it's alliased as "a".
Oh I had that section commented out because it wasn't needed with ur original script. I joined it to vp_person c on sub1.clientusername = c.personnum to get the Last Approver Name.
But now that i add it back in, it takes 1 min to return 1 row, performance is twice as fast as my original but still slow. Here is the script right now....
select b.homelaborlevelname1 as company
,b.homelaborleveldsc1 as company_descr
,b.homelaborleveldsc3 as location
,b.personnum as emplid
,b.personfullname as employee
,e.personfullname as prev_approver
,d.enteredondtm as prev_approval_time
--,c.personfullname as last_approver
,sub1.enteredondtm as last_approval_time
from vp_person b
inner join
(select -- get max Supervisor Approval row within past 2 weeks
personfullname,
personnum,
wfcaudittype,
max(enteredondtm) as enteredondtm
from
vp_wfcaudit
where
enteredondtm between dateadd(d,-14,getdate()) and getdate()
and
wfcaudittype = 'Supervisor approval'
and
personnum != clientusername
group by
personfullname,
personnum,
wfcaudittype) Sub1
on b.personfullname = sub1.personfullname
and b.personnum = sub1.personnum
inner join
vp_wfcaudit a
on a.personnum = sub1.personnum
and a.enteredondtm = sub1.enteredondtm
and a.wfcaudittype = 'Supervisor approval'
inner join
vp_person c
on a.clientusername = c.personnum -- get last approver name
inner join
vp_wfcaudit d
on d.personnum = b.personnum
and d.enteredondtm =
(select max(enteredondtm) from vp_wfcaudit -- get max Supervisor Approval row prior to above row
where personfullname = d.personfullname
and personnum = d.personnum
and wfcaudittype = d.wfcaudittype
and clientusername <> b.personnum
and enteredondtm < sub1.enteredondtm)
and d.wfcaudittype = 'Supervisor approval'
inner join vp_person e on d.clientusername = e.personnum -- get previous approver name
where b.employmentstatus = 'active'
and b.homelaborlevelname1 not in ('Test01','uis','dlx','art','rtl','lfs','mcm','rad')
and b.personnum = '00004486'
and e.personnum <> c.personnum -- only include where previous approver is different than current approver
order by company, employee;
I was playing around with splitting my original script and using a temp table and it takes 15 sec to return 1 row which I a very pleased with but thats still for 1 row. If I remove the "b.personnum =" in the WHERE clause to run for everyone, it takes about 6 min, which is still good compared to 3 hrs on my original script. Anyone has any better ideas? I am still new to T-SQL and am learning as I go along.
btw, thanks for everyone's responses. Much appreciated!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply