Delete from query takes long time

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 2000 Forums

SQL Server Development (2000)

Delete from query takes long time

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-06-24 : 11:57:34

Hi,

I tested a deletion of records in sql2000 on a new machine, and it took 3 minutes. Now that I do it on the 'real' machine i'm already waiting 35 minutes in fat and its still running...
there is a table in it with 4.500.000 records...
should i wait or should i stop query (gives problems i think...).
i think i got major problem here, is there any way to empty tables (before doing dts) faster?

greetings,

Joris

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-24 : 13:15:17

You can "truncate" them .
truncate table <table_name>

Check this http://msdn.microsoft.com/en-us/library/ms177570.aspx

EDIT : only when you want to delete everything from the table

quote:
Originally posted by djorre

Hi,

I tested a deletion of records in sql2000 on a new machine, and it took 3 minutes. Now that I do it on the 'real' machine i'm already waiting 35 minutes in fat and its still running...
there is a table in it with 4.500.000 records...
should i wait or should i stop query (gives problems i think...).
i think i got major problem here, is there any way to empty tables (before doing dts) faster?

greetings,

Joris

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 13:42:14

if you're looking for full deletion of table records use TRUNCATE TABLE rather than DELETE. however, you shouldnt have any FK relationship defined to your table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-24 : 14:51:53

Now it is too late for this answer but for the future:
You can stop a deleting query and then you have to wait because a rollback takes time then.
But not really a problem to stop a running query.

And in case of using truncate I must say that you should know what you are doing because truncate resets the value for indentity column.
Maybe that is what you want but maybe not.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 15:07:01

You can always delete records in smaller chunks.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-25 : 05:23:39

Here is an example of what Peso suggests


declare @rowcount int
set @rowcount=10000
While @rowcount>0
begin
	set rowcount 10000
	delete from your_table
	set @rowcount=@@rowcount
end

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 05:27:23

Don't forget to reset the ROWCOUNT option afterwards.

SET ROWCOUNT 10000

WHILE @@ROWCOUNT > 0
	DELETE
	FROM	YourTable

SET ROWCOUNT 0

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-25 : 05:44:31

quote:
Originally posted by Peso

Don't forget to reset the ROWCOUNT option afterwards.
SET ROWCOUNT 10000

WHILE @@ROWCOUNT > 0
DELETE
FROM YourTable

SET ROWCOUNT 0

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

Yes. It is

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-25 : 05:49:48

quote:
Originally posted by Peso

Don't forget to reset the ROWCOUNT option afterwards.
SET ROWCOUNT 10000

WHILE @@ROWCOUNT > 0
DELETE
FROM YourTable

SET ROWCOUNT 0

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

Setting Rowcount to 10000 wont make @@ROWCOUNT to have value more than zero. There is always possibility it is 0
Either I would use variable or


SET ROWCOUNT 10000
	DELETE
	FROM	YourTable

WHILE @@ROWCOUNT > 0
	DELETE
	FROM	YourTable

SET ROWCOUNT 0

[/code]

Madhivanan

Failing to plan is Planning to fail

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-06-26 : 10:44:50

Thanks for all the replies!

Like 'a miracle', after posting this toppic I went back to the server and the statements were executed :o, took 35 minutes... I would have waited untill 45 so I'm lucky. Because we needed to pull all data from the new server (which was tested) to the old back again, I created a DTS package with the wizard and have this SOP:

DisableAllConstraints (else I get FK_probs executing DTS0
Delete from Tables (all tables)
Execute DTS Package New_To_Old
EnableAllConstraints

Doing the procedure from old to new server took 10 minutes.
Doing the procedure from new to old server took 60 minutes, while everything except hardware (6 years older) was the same.

I would like to know a faster way doing this table-conversions but I think there isn't? Yes the deleting in rowcounts maybe... or maybe simply script the database, delete it and than rebuild it? But I think the logins/users need to be reconfigured then.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources