DELETE Query taking time - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

DELETE Query taking time

Author

Topic

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-08-03 : 07:12:00

In my application i have an automated process, which deletes certain number of records from a table (containing approx: 4000000 records.)

The problem im facing is that, it takes unnecessarily longer times to delete just 10000 records from the table.

Any ideas why does DELETE consumes time to execute.
I have indexed(NonClustered) the column referencing in the DELETE query.

Regards
Srivatsa

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-08-03 : 08:00:50

I found this post suggested by Peso...
Hopefully i shall try the approaches mentioned here ...

Delete in smaller chunks.

First of all, there is a great FAQ here
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210[/url]

Great examples found here
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77084[/url]

and here
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83525[/url]

quote:
Originally posted by srivatsahg

In my application i have an automated process, which deletes certain number of records from a table (containing approx: 4000000 records.)

The problem im facing is that, it takes unnecessarily longer times to delete just 10000 records from the table.

Any ideas why does DELETE consumes time to execute.
I have indexed(NonClustered) the column referencing in the DELETE query.

Regards
Srivatsa

Haseet
Starting Member

6 Posts

Posted - 2009-08-04 : 00:58:03

You can use threading.

e.g. if you have 1000 records, you can create 2 threads. First thread deletes records from 1 to 500 and second thread deletes records from 501 to 1000.

Multiple threads will connect to sql server in different connections. You can take advantage of multiple connections.

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-08-04 : 10:28:14

Hi Haseet
Could you please provide me a simple sample...

Thanks in advance
Srivatsa

quote:
Originally posted by Haseet

You can use threading.

e.g. if you have 1000 records, you can create 2 threads. First thread deletes records from 1 to 500 and second thread deletes records from 501 to 1000.

Multiple threads will connect to sql server in different connections. You can take advantage of multiple connections.

jeremygiaco
Starting Member

14 Posts

Posted - 2009-08-04 : 15:57:24

Could you please post your delete statement?

Jeremy Giaco

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-08-05 : 05:18:05

It is as simple as a normal delete statement

DELETE TOP(1000) from AllEvents Where ID IN (Select ID from ToDeleteTable)

where ToDeleteTable contains the entries to be deleted in the AllEvents table..

quote:
Originally posted by jeremygiaco

Could you please post your delete statement?

Jeremy Giaco

Haseet
Starting Member

6 Posts

Posted - 2009-08-10 : 03:15:33

You should avoid using IN clause...

Try this

DELETE TOP(1000) from AllEvents A Where exists (Select 1 from ToDeleteTable D where D.ID = A.Id)

Haseet
Starting Member

6 Posts

Posted - 2009-08-10 : 03:27:07

Regarding threading, you can get the code from google...

I can give you the logic..

e.g. suppose 1000 records are to be deleted in threading,
and you want to delete 100 records in each thread.
So you will have 10 threads.
write a for loop...create a new thread in each iteration and pass startindex and endindex as parameter..e.g. in first iteration 1 to 100, in second iteration 101 to 200..like that.
The method which is called in this thread will take this index values as parameters and delete records from 1 to 100, 101 to 200..in parallel(asynchronously)..so it will be much faster.

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-08-10 : 07:03:26

The following query throws an error...
Incorrect syntax near 'A'

quote:
Originally posted by Haseet

You should avoid using IN clause...

Try this

DELETE TOP(1000) from AllEvents A Where exists (Select 1 from ToDeleteTable D where D.ID = A.Id)

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-08-10 : 07:10:19

The table structure is this way..Even deleting 100 rows takes time


ID        - uniqueidentifier NOT NULL, PK 
FK_ID     - uniqueidentifier NOT NULL,
DateTime  - datetime NOT NULL,
Eventcode - int NOT NULL,
Info      - varchar(max)

Non clustered indexes on the fields DateTime,EventCode,ID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 07:23:20

How long is "100 rows takes time"?

N 56°04'39.26"
E 12°55'05.63"

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-08-10 : 07:41:09

Hello Peso
Deleting 100 rows takes 6 seconds...which is way too slow for my application...

quote:
Originally posted by Peso

How long is "100 rows takes time"?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 07:46:50

Deleting 100 records out of 4,000,000 is considered "slow" when taking 6 seconds?
With a nonclustered index only? Or do you have 3 separate indexes?

How long time is acceptable?

N 56°04'39.26"
E 12°55'05.63"

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-08-10 : 07:58:58

Hello Peso

Yes it is considered slow taking 6 secs for deleting 100 out of 400000 records. Sorry for the confusing statement, i meant i have three separate non-clustered indexes for the columns

Im looking for a duration <= 2seconds

Regards
Srivatsa

quote:
Originally posted by Peso

Deleting 100 records out of 4,000,000 is considered "slow" when taking 6 seconds?
With a nonclustered index only? Or do you have 3 separate indexes?

How long time is acceptable?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 08:04:04

What is the key element for deleting?
Records based on DateTime column?

N 56°04'39.26"
E 12°55'05.63"

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-08-10 : 08:07:44

Its No/Yes
i will be populating a temporary table(ToDeleteTable) depending on the events to be deleted for (say last 3 months).

Then using the entries from the table i delete using a query similar to this one...

DELETE TOP(1000) from AllEvents Where ID IN (Select ID from ToDeleteTable)

quote:
Originally posted by Peso

What is the key element for deleting?
Records based on DateTime column?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 08:10:44

Then you should make ID a clustered key for faster seek.
But making a clustered column based on a GUID is not a good idea.

N 56°04'39.26"
E 12°55'05.63"

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-08-10 : 08:14:47

Yes even i realise that a clustered index on a uniqueidentifier is not a good idea. The application database was developed that way.
Do you have any ideas/alternatives in this direction

Vatsa

quote:
Originally posted by Peso

Then you should make ID a clustered key for faster seek.
But making a clustered column based on a GUID is not a good idea.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 08:31:04

You can create a clustered index on the datetime column, if all deletes are based on age.

N 56°04'39.26"
E 12°55'05.63"

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-08-10 : 08:41:17

Would that help though i am not using it as a criteria in my DELETE query ??

quote:
Originally posted by Peso

You can create a clustered index on the datetime column, if all deletes are based on age.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 08:43:40

Probably not.
The reason the query takes time now, is that in your index heap you have to look up each and one matching record and then delete it. 6 seconds for 100 records out of 40,000,000 is not much in this case.

N 56°04'39.26"
E 12°55'05.63"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources