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.
Author |
Topic |
mcolli00
Starting Member
19 Posts |
Posted - 2011-02-15 : 11:42:16
|
Hi allI 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]ASDELETE FROM RunningJobs WHERE DATEADD(s, Timeout, StartDate) < GETDATE()ThanksMC |
|
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 thisselect * 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 |
|
|
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 |
|
|
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()MadhivananFailing to plan is Planning to fail |
|
|
X002548
Not Just a Number
15586 Posts |
|
yuanyelss
Starting Member
4 Posts |
Posted - 2011-02-19 : 00:28:26
|
unspammed |
|
|
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 |
|
|
|
|
|