Nested performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

The Yak Corral

Nested performance

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-05-24 : 10:13:43

I though that the nested performance hit was not that big of a deal untill I made this conversion.

I started out with a nested SP that compared one week of an employeee schedule table to a time clock punch table and looked for absences then mached up to an excused table.
It had about 1000 comparisons to do and output.

SELECT TimeClockPlus.dbo.EmployeeSchedules.RecordId, TimeClockPlus.dbo.EmployeeList.LastName + ' ' + TimeClockPlus.dbo.EmployeeList.FirstName As ename,
TimeClockPlus.dbo.EmployeeSchedules.EmployeeId, TimeClockPlus.dbo.EmployeeSchedules.TimeIn, TimeClockPlus.dbo.EmployeeSchedules.TimeOut,
dbo.Absence_excused.Calledoff,dbo.Absence_excused.excuse,
Points = case when dbo.Absence_excused.excuse is null and (dbo.Absence_excused.Calledoff is null or dbo.Absence_excused.Calledoff = 0)
Then '3'
when dbo.Absence_excused.excuse is null and dbo.Absence_excused.Calledoff = 1
Then '2'
when dbo.Absence_excused.excuse is not null and (dbo.Absence_excused.Calledoff = 0 Or dbo.Absence_excused.Calledoff is null)
Then '1/2'
when dbo.Absence_excused.excuse is not null and dbo.Absence_excused.Calledoff = 1
Then '0'
end
FROM TimeClockPlus.dbo.EmployeeSchedules INNER JOIN
TimeClockPlus.dbo.EmployeeList ON
TimeClockPlus.dbo.EmployeeSchedules.EmployeeId = TimeClockPlus.dbo.EmployeeList.EmployeeId LEFT OUTER JOIN
dbo.Absence_excused ON TimeClockPlus.dbo.EmployeeSchedules.RecordId = dbo.Absence_excused.RecordId
WHERE (TimeClockPlus.dbo.EmployeeSchedules.RecordId NOT IN
(SELECT TimeClockPlus.dbo.EmployeeSchedules.RecordId
FROM TimeClockPlus.dbo.EmployeeSchedules INNER JOIN
TimeClockPlus.dbo.EmployeeHours ON
TimeClockPlus.dbo.EmployeeSchedules.EmployeeId = TimeClockPlus.dbo.EmployeeHours.EmployeeId AND
TimeClockPlus.dbo.EmployeeSchedules.TimeIn BETWEEN DATEADD(hour, - 3, TimeClockPlus.dbo.EmployeeHours.TimeIn) AND
DATEADD(hour, 4, TimeClockPlus.dbo.EmployeeHours.TimeIn))) AND (TimeClockPlus.dbo.EmployeeSchedules.TimeIn < GETDATE()) AND
(TimeClockPlus.dbo.EmployeeList.Department IN
(SELECT Department
FROM user_ccc
WHERE ccuserid = suser_sname(suser_sid()))) AND (TimeClockPlus.dbo.EmployeeList.Suspend = 0) and
Timeclockplus.dbo.EmployeeSchedules.TimeIn Between Cast(@weekstart as datetime) and @Weekend And
Timeclockplus.dbo.EmployeeSchedules.TimeIn < Dateadd(hour,-1,Getdate())
AND (DATEPART(year, TimeClockPlus.dbo.EmployeeSchedules.TimeIn) > 2004)
ORDER BY TimeClockPlus.dbo.EmployeeList.Department, TimeClockPlus.dbo.EmployeeList.LastName

This took 27 seconds though the front end.
I rebuilt it useing temp tables.

SELECT Department
Into #department
FROM user_ccc
WHERE ccuserid = suser_sname(suser_sid())

SELECT TimeClockPlus.dbo.EmployeeSchedules.EmployeeId, TimeClockPlus.dbo.EmployeeSchedules.RecordId,
TimeClockPlus.dbo.EmployeeSchedules.TimeIn, TimeClockPlus.dbo.EmployeeSchedules.TimeOut
Into #schedule
FROM TimeClockPlus.dbo.EmployeeSchedules INNER JOIN
TimeClockPlus.dbo.EmployeeList ON TimeClockPlus.dbo.EmployeeSchedules.EmployeeId = TimeClockPlus.dbo.EmployeeList.EmployeeId INNER JOIN
dbo.#department ON TimeClockPlus.dbo.EmployeeList.Department = dbo.#department.Department AND
TimeClockPlus.dbo.EmployeeList.Suspend = 0 AND TimeClockPlus.dbo.EmployeeSchedules.TimeIn BETWEEN CAST(@weekstart AS datetime) AND
@Weekend AND TimeClockPlus.dbo.EmployeeSchedules.TimeIn < DATEADD(hour, - 1, GETDATE()) AND DATEPART(year,
TimeClockPlus.dbo.EmployeeSchedules.TimeIn) > 2004

SELECT dbo.#schedule.RecordId
Into #schedule2
FROM dbo.#schedule INNER JOIN
TimeClockPlus.dbo.EmployeeHours ON
dbo.#schedule.EmployeeId = TimeClockPlus.dbo.EmployeeHours.EmployeeId AND
dbo.#schedule.TimeIn BETWEEN DATEADD(hour, - 3, TimeClockPlus.dbo.EmployeeHours.TimeIn) AND
DATEADD(hour, 4, TimeClockPlus.dbo.EmployeeHours.TimeIn)

SELECT dbo.#schedule.EmployeeId, dbo.#schedule.RecordId, dbo.#schedule.TimeIn, dbo.#schedule.TimeOut
Into #schedule3
FROM dbo.#schedule LEFT OUTER JOIN
dbo.#schedule2 ON dbo.#schedule.RecordId = dbo.#schedule2.RecordId
WHERE dbo.#schedule2.RecordId IS NULL

SELECT dbo.#schedule3.RecordId, TimeClockPlus.dbo.EmployeeList.LastName + ' ' + TimeClockPlus.dbo.EmployeeList.FirstName As ename,
dbo.#schedule3.EmployeeId,dbo.#schedule3.TimeIn,dbo.#schedule3.TimeOut,
dbo.Absence_excused.Calledoff,dbo.Absence_excused.excuse,
Points = case when dbo.Absence_excused.excuse is null and (dbo.Absence_excused.Calledoff is null or dbo.Absence_excused.Calledoff = 0)
Then '3'
when dbo.Absence_excused.excuse is null and dbo.Absence_excused.Calledoff = 1
Then '2'
when dbo.Absence_excused.excuse is not null and (dbo.Absence_excused.Calledoff = 0 Or dbo.Absence_excused.Calledoff is null)
Then '1/2'
when dbo.Absence_excused.excuse is not null and dbo.Absence_excused.Calledoff = 1
Then '0'
end
FROM dbo.#schedule3 INNER JOIN
TimeClockPlus.dbo.EmployeeList ON
dbo.#schedule3.EmployeeId = TimeClockPlus.dbo.EmployeeList.EmployeeId LEFT OUTER JOIN
dbo.Absence_excused ON dbo.#schedule3.RecordId = dbo.Absence_excused.RecordId

This runs in about 1/2 a second.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-24 : 22:35:52

OK, first well, duh, second why isn't this a view, third, I've had a few cockails, fourth, I've had a few cockails, actually 4th, this is not a yk corralll issue, nless, i't the fotht issues

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-24 : 22:36:53

damn cocktails...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-05-25 : 08:24:52

Drinking at work again Brett?
LOL
I just could not belive the effect I thought you guys/gals were talking about say 10% performance lag.

Of course I rarely use nests anyway but wow!

Jim
Users <> Logic

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-25 : 09:42:46

I haven't had any cocktails (yet) but Bretts still probably better after a few than I am sober...but anyway:
Seems a real comparison would be if you change your NOT IN (select stmnt) to a Left OUTER JOIN (derived table). That way the logic in your "nested" query would be closer to the logic in the final, 1/2 second statment. May still not make a difference but I'd find it intersting.

Definately not yak forum stuff though :) except the drinking part...

Be One with the Optimizer
TG

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-05-25 : 11:11:32

Actually I don't know why I put this in here.

Maybee I am the one who has been Drinking.

Hey Damian please move this so Brett stops picking on me.

And he is on my side of the seat he's over the line!!!!

TG that's 8 seconds better but not good enough.

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources