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
 General SQL Server Forums
 New to SQL Server Programming
 revise Delete Query to reduce table scan

Author  Topic 

mcolli00
Starting Member

19 Posts

Posted - 2011-02-15 : 11:42:16
Hi all
I am getting an alert with my idera tool showing that there is a table scan occurring with the script below and that I should revise it. However, I am foggy on how to pull the column out of the predicate so that that the query does exactly the same but with a different logic flow.Will you show me how to revise this?

CREATE PROCEDURE [dbo].[deleteExpiredJobs]
AS
DELETE FROM RunningJobs WHERE DATEADD(s, Timeout, StartDate) < GETDATE()

Thanks
MC

Sachin.Nand

2937 Posts

Posted - 2011-02-15 : 12:40:02
A function on a column having an index will cause that index to be unused.

Change your to this

select * FROM RunningJobs WHERE StartDate > DATEADD(s, -Timeout,GETDATE())

Please verify the result using the select clause and then only convert it to a delete statement.


PBUH

Go to Top of Page

mcolli00
Starting Member

19 Posts

Posted - 2011-02-16 : 12:14:01
Thanks for your code. I tried it but I am still getting a table scan even with an index on the startdate column.

Misty
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-18 : 10:16:31
Try this and see

select * FROM RunningJobs WHERE Startdate<getdate() and DATEADD(s, Timeout, StartDate) < GETDATE()

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-02-18 : 10:32:50
Do you have an index on startdate?

Post the DDL



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


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

yuanyelss
Starting Member

4 Posts

Posted - 2011-02-19 : 00:28:26
unspammed
Go to Top of Page

Sachin.Nand

2937 Posts

Posted - 2011-02-19 : 03:19:19
quote:
Originally posted by mcolli00

Thanks for your code. I tried it but I am still getting a table scan even with an index on the strtdate column.

Misty



My best bet here would be that your query seems to be covering more than 20% of the total rows in the table.
If that's the case then the optimizer will go for a scan even if an index is defined on the column.

PBUH

Go to Top of Page
   

- Advertisement -