Reducing speed of 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)

Reducing speed of query

Author

Topic

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-01-24 : 04:31:17

Hi,

I have a job that runs every morning and extracts records from the database. If a record has met the criteria to be extracted,
it is moved to the log table [TimeExtractAuditLog], time stamped and given a unique [TimeID].

The problem is that the files in the log table are now too much and its taking SQL a long time to query these becuase it has to look at every
record in the log table and to ensure no duplicate records are inserted incase a job is re-run.

Any ideas of how i can improve the speed of the query when checking the log table, what's the best practice. Thats what i have in my where clause to check the log tables?

@FromDate - Input Parameter Dates
@ToDate

WHERE
(time.datecreated >= @FromDate
AND time.datecreateded < @ToDate
AND time.id NOT IN (SELECT TimeID FROM MISCustomTasks.dbo.tblTimeExtractAuditLog)-- Exclude any time that has already been sent for the date range

AND time.minutes <> 0) -- exclude duration = 0

OR (time.id IN (SELECT TimeID FROM MISCustomTasks.dbo.tblTimeExtractUnsentLog)-- And Include any previous time that still hasn't been sent, as in tblTimeExtractUnsentLog, but double-check not in tblTimeExtractAuditLog

AND time.minutes <> 0) -- exclude duration = 0

Many Thanks

Marcus

I learn something new everyday.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-24 : 04:38:03

why don't you have a flag in the table to indicate that it has been extracted before. Then you don't have to check the log table just this source table


WHERE extract = 0

KH
[spoiler]Time is always against us[/spoiler]

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-01-24 : 06:23:01

Thank you KH. The data is logged in the audit table for later 're-use. I am not sure if flagging the data would be the practice as there are thousands of records existing already in audit log.

Marcus

I learn something new everyday.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-24 : 08:15:13

alternatively, change your existing query to use EXISTS() instead of IN


WHERE
(
    time.datecreated >= @FromDate
AND time.datecreateded < @ToDate
AND NOT EXISTS (SELECT * FROM MISCustomTasks.dbo.tblTimeExtractAuditLog x WHERE x.TimeID = time.id)
AND time.minutes <> 0
)

and make sure you have index on the TimeID

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources