SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 revise Delete Query to reduce table scan
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mcolli00
Starting Member

19 Posts

Posted - 02/15/2011 :  11:42:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 02/15/2011 :  12:40:02  Show Profile  Reply with Quote
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 - 02/16/2011 :  12:14:01  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 02/18/2011 :  10:16:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 02/18/2011 :  10:32:50  Show Profile  Reply with Quote
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

China
4 Posts

Posted - 02/19/2011 :  00:28:26  Show Profile  Visit yuanyelss's Homepage  Reply with Quote
unspammed

Edited by - yuanyelss on 02/19/2011 00:29:24
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 02/19/2011 :  03:19:19  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000