Board index » delphi » Best way to delete 3.000.000 records under load

Best way to delete 3.000.000 records under load

"Delete" statment log each row deletion, use "truncate table" instead, its
more fast,  but you can't undo then operation.
 

Re:Best way to delete 3.000.000 records under load


Hi,

I have to delete 3.000.000 records from a statistic table on MSSQL2000,
but the SQL-Server has still a lot of work to do. Whats the safest and quickest
way to do that?

thanks a lot,
carsten willems

Re:Best way to delete 3.000.000 records under load


Hi,

there are 5M records, and I want to delete the first 3M. Dropping and Cretae/
Import would do it, but I would not like to take the database offline for so long
time. Is there another way?

thanks,
carsten willems

Re:Best way to delete 3.000.000 records under load


Do a DTS package, and unload the "not to be deleted" records into a text
file, in the cluster primary key order (if there is one). Then, TRUNCATE the
table, and BULK INSERT FROM the text file, using the ORDER(...) of the
Cluster Primary key.

Good luck

"Carsten Willems" <carsten.will...@i-want-no-spam.de> escribi en el mensaje
news:3db86453$1@newsgroups.borland.com...

Quote
> Hi,

> there are 5M records, and I want to delete the first 3M. Dropping and
Cretae/
> Import would do it, but I would not like to take the database offline for
so long
> time. Is there another way?

> thanks,
> carsten willems

Re:Best way to delete 3.000.000 records under load


Quote
> use "truncate table" instead, its more fast,  but you can't undo

Truncate table won't work if you have data that you do NOT want to
remove.  I got the impression that there are more than 3,000,000 records
in the table and he needs to delete 3,000,000 and leave other data
there.

David R.

Re:Best way to delete 3.000.000 records under load


You could do insert into...select from, into another table, then delete the
original table, or truncate it, then move the records back again.

In general this would be faster than unloading to disk and loading from disk
but it depends on memory and disk config primarily. If you can move the data
to a table on another filegroup/spindle, this will reduce IO contention. If
its done a lot then consider adding the copy out table to its own file group
for perf.

-Euan

Quote
"Carsten Willems" <carsten.will...@i-want-no-spam.de> wrote in message

news:3db84be8@newsgroups.borland.com...
Quote
> Hi,

> I have to delete 3.000.000 records from a statistic table on MSSQL2000,
> but the SQL-Server has still a lot of work to do. Whats the safest and
quickest
> way to do that?

> thanks a lot,
> carsten willems

Re:Best way to delete 3.000.000 records under load


thanks to all of you

cw

Re:Best way to delete 3.000.000 records under load


Do you have lots of indexes on this table?

Other Threads