Is it supposed to take this long to delete? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Is it supposed to take this long to delete?

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2009-01-07 : 11:24:39

I'm using this delete statement to delete entries in a huuuuuge table.

Specs of the table.

21,500,000 rows
size is 7.8gb

index is 5gb

I ran the delete statement yesterday, and it's telling me that it is still running, it's been 22 and a half hours.

Does this sound right or has something been royally screwed up?

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-07 : 11:32:18

i have deleted tables with more records than that in it and it never took 22 hours. maybe im wrong but they must be something wrong with that

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-01-07 : 11:34:13

Possibly, if you are deleting all the data, do you need to log the delete? If not then you could use truncate table, which will not write to the log (therefore not having to expand the log by ~13Gb).

Use this only if you are deleting ALL the data in the table.

sqlchiq
Posting Yak Master

133 Posts

Posted - 2009-01-07 : 11:36:57

I'm not deleting the entire table, only elements that are unnecessary.

its not a simple delete from table statement

this is the statement

DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY vin_number ORDER BY executiondatetime) AS Inc,ROW_NUMBER() OVER (PARTITION BY vin_number ORDER BY executiondatetime DESC) AS Dec
FROM autocarinventory)t
WHERE t.Inc<>1 AND t.Dec <>1

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-07 : 11:38:43

Better to delete in batches if you have to delete huge records otherwise truncate if need to delete all data. Did you see in Activity monitor what it is doing or with sp_who2 active?

sqlchiq
Posting Yak Master

133 Posts

Posted - 2009-01-07 : 11:53:31

I do not know how to delete in batches, could you briefly explain that to me? or maybe alter my statement into a batch

I'm not sure what activity monitor or sp_who2 is, sorry I'm kind of new

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 11:56:42

quote:
Originally posted by sqlchiq

I do not know how to delete in batches, could you briefly explain that to me? or maybe alter my statement into a batch

I'm not sure what activity monitor or sp_who2 is, sorry I'm kind of new

just take the records you want to delete in batches based on value of primary key . like if you've for example 1 lakh record delete them in batches of say 10,000 so that first delete 1 to 10000, then 10.001 to 20,000,... upto 100000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 11:58:37

also sp_who2 gives you details of various processes running in your database. with this you can understanding what queries are in runnable states, whether any blocking is caused, who all are running queries,...

sqlchiq
Posting Yak Master

133 Posts

Posted - 2009-01-07 : 12:04:04

quote:
Originally posted by visakh16

quote:
Originally posted by sqlchiq

ot sure what activity monitor or sp_who2 is, sorry I'm kind of new

just take the records you want to delete in batches based on value of primary key . like if you've for example 1 lakh record delete them in batches of say 10,000 so that first delete 1 to 10000, then 10.001 to 20,000,... upto 100000

The only problem with this is that, the delete is dependent upon elements that are present through out the entire table and not just in a certain portion.

You helped me write that statement, if u recall it looks up the first and the last date of entries with a certain ID, and it deletes all the entries in between. The first entry could be 5 months ago, and the latest date could be today, therefore it would span the entire table, no?

in this case i dont think the batch would work, but I could be wrong?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-07 : 12:12:59

try this

select t.* into #temp
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY vin_number ORDER BY executiondatetime) AS Inc,ROW_NUMBER() OVER (PARTITION BY vin_number ORDER BY executiondatetime DESC) AS Dec, *
FROM autocarinventory)t
WHERE t.Inc = 1 AND t.Dec=1

truncate table autocarinventory

insert into autocarinventory
select required columns from #temp

First make sure #temp is populated with correct data or not before truncating ur table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 12:18:30

quote:
Originally posted by sqlchiq

quote:
Originally posted by visakh16

quote:
Originally posted by sqlchiq

ot sure what activity monitor or sp_who2 is, sorry I'm kind of new

just take the records you want to delete in batches based on value of primary key . like if you've for example 1 lakh record delete them in batches of say 10,000 so that first delete 1 to 10000, then 10.001 to 20,000,... upto 100000

The only problem with this is that, the delete is dependent upon elements that are present through out the entire table and not just in a certain portion.

You helped me write that statement, if u recall it looks up the first and the last date of entries with a certain ID, and it deletes all the entries in between. The first entry could be 5 months ago, and the latest date could be today, therefore it would span the entire table, no?

in this case i dont think the batch would work, but I could be wrong?

ok...i remember that. what you need to do is to get ids of all the entries except first and latest to another temp table with an additional identity column. then using this table join onto main table and take batches based on identity field as specified earlier.

sqlchiq
Posting Yak Master

133 Posts

Posted - 2009-01-07 : 12:26:36

[code]
59 RUNNABLE Angora2-PC\Angora 2 ANGORA2-PC . connotate DELETE 573678 4533945 01/05 22:58:15 Microsoft SQL Server Management Studio - Query 59 0

[/code]

that's what I got when I ran the sp2_who

I guess it's not a big deal as long as I know for sure for sure that it will finish successfully.

Will doing this batch thing actually make it complete faster?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 12:32:34

quote:
Originally posted by sqlchiq

59 RUNNABLE Angora2-PC\Angora 2 ANGORA2-PC . connotate DELETE 573678 4533945 01/05 22:58:15 Microsoft SQL Server Management Studio - Query 59 0

that's what I got when I ran the sp2_who

I guess it's not a big deal as long as I know for sure for sure that it will finish successfully.

Will doing this batch thing actually make it complete faster?

you can make a try

sqlchiq
Posting Yak Master

133 Posts

Posted - 2009-01-07 : 12:43:00

quote:
Originally posted by visakh16

quote:
Originally posted by sqlchiq

[code]

you can make a try

not sure how exactly to use it?

like a try catch type of statement?

I looked it up and it seems like what it does is it performs another action when it reaches an error

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-07 : 12:50:42

quote:
Originally posted by sqlchiq

quote:
Originally posted by visakh16

quote:
Originally posted by sqlchiq

[code]

you can make a try

not sure how exactly to use it?

like a try catch type of statement?

I looked it up and it seems like what it does is it performs another action when it reaches an error

I think visakh mean to say Follow the steps as he suggested for batch deletion but not To Use try and catch blocks

sqlchiq
Posting Yak Master

133 Posts

Posted - 2009-01-07 : 12:53:35

Ohh, got it

sqlchiq
Posting Yak Master

133 Posts

Posted - 2009-01-07 : 13:10:12

since its already been processing for over 24 hours I'm just going to let it ride and see if it works, after all I still hear the harddrive working and it still says executing.

What should be the threshold for how long I should wait before I try another solution?

sqlchiq
Posting Yak Master

133 Posts

Posted - 2009-01-07 : 20:41:37

Its been hours and it's still on this "Runnable" status

I'm guessing that means its not even running???

try plan B?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 02:33:23

quote:
Originally posted by sqlchiq

Its been hours and it's still on this "Runnable" status

I'm guessing that means its not even running???

try plan B?

yup...try it. no point in keeping it running

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 02:52:09

Can you post the table structure?
Do you have an identity column?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 03:00:32

Having two ROW_NUMBER() functions in the derived table is costsome.
If I understand you correctly, you want to delete all records for any vin_number, except for oldest and newest executiondate?

SELECT TOP 0	*
INTO		#Temp
FROM		AutocarInventory

INSERT	#Temp
	(<col-list here>)
SELECT	<col-list here>
FROM	(
		SELECT	*,
			ROW_NUMBER() OVER (PARTITION BY vin_number ORDER BY ExecutionDatetime) AS RecID
		FROM	AutocarInventory
	) AS f
WHERE	RecID = 1

INSERT	#Temp
	(<col-list here>)
SELECT	<col-list here>
FROM	(
		SELECT	*,
			ROW_NUMBER() OVER (PARTITION BY vin_number ORDER BY ExecutionDatetime DESC) AS RecID
		FROM	AutocarInventory
	) AS f
WHERE	RecID = 1

TRUNCATE TABLE	AutocarInventory

INSERT	AutocarInventory
	(<col-list here>)
SELECT	<col-list here>
FROM	#Temp

DROP TABLE	#Temp

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources