delete statement taking forever to run

i have a very simple delete statement where I am deleting from 1 table with a simple where clause and it runs and run an does not finish. When I select the records it runs very quickly. any reason why this would happen?
vbnetcoderAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Create nonclustered index ix_createddate on yourtablename(createddate)
0
 
Pawan KumarDatabase ExpertCommented:
can u send the query
0
 
NorieVBA ExpertCommented:
Post the SQL for the query.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If this is against a SQL Server back end, more than likely what's happening is that Access (JET or ACE), is passing a DELETE to SQL for each row.

 This can happen based on the criteria you specify where it has to look at something else before it can decide to delete the row or not.

 In that case, you can either:

a. Change the criteria

b.  Execute it as a pass-through query (which sends it server side for execution).

Jim.
0
 
Pawan KumarDatabase ExpertCommented:
The issue may be a missing index or some blocking. Can you attach the estimated execution plan.
0
 
vbnetcoderAuthor Commented:
jim how do I Execute it as a pass-through query (which sends it server side for execution).?
0
 
vbnetcoderAuthor Commented:
DELETE FROM  TABLE
      WHERE  cast(convert(char(11), createdate, 113) AS DATETIME)=cast(convert(char(11), GETDATE(), 113) AS DATETIME)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In the Access query designer, right client in the blank table area at the top, select "SQL Specific" and then "Pass-Through".

Once you do that, paste in the SQL.   The SQL needs to be in the dialect of the back end your using as it will be executing there (no VBA expressions, JET Specific SQL like GROUP BY, etc) , and it needs to be complete (you can't have it refer to a form for example).

Jim.
0
 
Pawan KumarDatabase ExpertCommented:
You are using functions around the column in the where clause and that is leading to the entire table scan. You should be using like below. The below query should not take much time.

--
DELETE FROM  TABLE 
WHERE  
createdate >= CAST(GETDATE() AS DATE)   
AND createdate < CAST(GETDATE()+1 AS DATE)   
--

Open in new window

0
 
vbnetcoderAuthor Commented:
Pawan Kumar that query is taking forever too. I will try at a different time of the day
0
 
Pawan KumarDatabase ExpertCommented:
It should not take forever time.
can you send me the estimated execution plan?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Pawan Kumar that query is taking forever too. I will try at a different time of the day
That means you don't have any index on the createdate column so the engine is executing a table scan (worst performance) instead of an index seek (better performance).
Anyway, I don't even understand why do you need to convert and cast as it seems to me that you can perform a direct comparision:
DELETE FROM  TABLE 
WHERE  createdate >= cast(GETDATE() AS DATE())

Open in new window

NOTE: I'm assuming the table doesn't have rows created in the future (createdate > TODAY)
0
 
Pawan KumarDatabase ExpertCommented:
Check the execution plan , SQL must be mentioning the missing index there. So create index on createdDate column.
0
 
vbnetcoderAuthor Commented:
how do I add a index on created date?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
how do I add a index on created date?
First check if you don't have one already.
0
 
vbnetcoderAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.