Slow nested 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 2008 Forums

Transact-SQL (2008)

Slow nested query

Author

Topic

cwazywabbit
Starting Member

2 Posts

Posted - 2011-04-01 : 09:42:18

I have a query containing a nested query, which both by themselves are instant. However, together the query is extremely slow (16 seconds)

SELECT t1.ID, t2.status, t2.error, t1.processid, t1.details, t1.verify,
CONVERT(CHAR(19),DATEADD(Hour,DATEDIFF(Hour,GETUTCDATE(),GETDATE()),t1.starttime), 120) AS starttime,
CONVERT(CHAR(19),DATEADD(Hour,DATEDIFF(Hour,GETUTCDATE(),GETDATE()),t1.endtime), 120) AS endtime,
CONVERT(CHAR(19),DATEADD(Hour,DATEDIFF(Hour,GETUTCDATE(),GETDATE()),t1.lastchecked),120) AS lastchecked
FROM jobrun t1 LEFT JOIN status t2 ON (t2.ID=t1.status_ID)
WHERE t1.joblist_ID=86 AND (t1.verify>0 OR t1.ID = (SELECT TOP 1 ID FROM jobrun WHERE joblist_ID=86 ORDER BY starttime DESC)) ORDER BY t1.starttime DESC

This returns everything that has not been verified (verify > 0) and/or the most recent record.

Any help would be greatly appreciated.

Lee

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-01 : 10:04:31

COuld try putting the value in a variable but it is probably the OR clause that is giving you fits. If putting it in a variable doesn't help can you post the DDL with indexes and possibly an execution plan?

(Also do you need to conver the dates to strings?):

DECLARE @JonRunID INT

SET @JonRunID = (SELECT TOP 1 ID FROM jobrun WHERE joblist_ID=86 ORDER BY starttime DESC)

SELECT 
    t1.ID, 
    t2.status, 
    t2.error, 
    t1.processid, 
    t1.details, 
    t1.verify, 
CONVERT(CHAR(19),DATEADD(Hour,DATEDIFF(Hour,GETUTCDATE(),GETDATE()),t1.starttime), 120) AS starttime, 
CONVERT(CHAR(19),DATEADD(Hour,DATEDIFF(Hour,GETUTCDATE(),GETDATE()),t1.endtime), 120) AS endtime, 
CONVERT(CHAR(19),DATEADD(Hour,DATEDIFF(Hour,GETUTCDATE(),GETDATE()),t1.lastchecked),120) AS lastchecked 
FROM 
    jobrun t1 
LEFT JOIN 
    status t2 
    ON (t2.ID=t1.status_ID) 
WHERE 
    t1.joblist_ID=86 
    AND (t1.verify>0 OR t1.ID = @JonRunID)
    
ORDER BY t1.starttime DESC

cwazywabbit
Starting Member

2 Posts

Posted - 2011-04-01 : 14:44:50

Thank you very much! Worked perfectly.

L

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources