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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
can u send the query
0
NorieAnalyst Assistant Commented:
Post the SQL for the query.
0
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Pawan KumarDatabase ExpertCommented:
Create nonclustered index ix_createddate on yourtablename(createddate)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.