Urgent help with Query! - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Urgent help with Query!

Author

Topic

psmith98752
Starting Member

3 Posts

Posted - 2009-01-10 : 13:22:51

Hello

In my tables, a column is used to identify "groups" of records - ie stores multiple users details in the one database.

In the query, I search that column so to only return records for one user at a time. This query executes in under a second for most users however for a few it takes around 8 seconds - far too long as this is for a website.

I checked the Query Execution Plan and noted that the fast queries use Table Spooling and a Hash Match Join.

The query is:

SELECT COUNT(main.rm) As Total, day, time
FROM main, rooms WHERE rooms.rm = main.rm
AND rooms.uname = 'CHANGING_THIS' AND main.uname = 'CHANGING_THIS'
AND (main.room NOT IN( SELECT rm FROM bod
WHERE main.time = bod.time AND main.rm = booked.rm
AND main.day = booked.sday
AND sdate >= DATEADD(dd, DATEDIFF(dd,0,GetDate()+0), 0)
AND sdate <= DATEADD(dd, DATEDIFF(dd,0,GetDate()+0+6), 0)
AND bod.uname = 'CHANGING_THIS' GROUP BY sdate, bod.rm, sday))
GROUP BY day, time

Any ideas?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-10 : 13:54:22

Try WHERE NOT EXISTS instead of NOT IN.
In much cases it's real faster.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 14:12:35

or even try inner join with second query on rm field with index on it

psmith98752
Starting Member

3 Posts

Posted - 2009-01-10 : 14:16:00

Hi, I did try NOT EXISTS, no difference. Also tried inner join.

Can anyone explain this? I copied the data to my local pc to try and work out whats happening. If I take a uname that runs quick, and rename it in all tables to something else, when I then run that, its slow! - This must be related

Thanks so far for the quick replies!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 14:44:06

what does execution plan show?

psmith98752
Starting Member

3 Posts

Posted - 2009-01-10 : 16:33:23

The fast queries show:

Nested Loops (Left Anti Semi Join)
CONNECTED TO
1) Hash Match (Inner Join)
2) Table Spool (Lazy Spool)

The slow queries:

Nested Loops (Left Anti Semi Join)
CONNECTED TO
1) Nested Loops (inner join)
2) Clustered Index Scan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources