473,388 Members | 936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

Large Data needs to be Deleted??? HOW???

Sam
I have a database that is 70GB big. One of the tables has over 350
million rows of data. I need to delete about 1/3 of the data in that
one table.

I was going to use a simple delete command to delete the unnessacay
data.
Something like

Delete from tablename where columname > '100'

However, it takes SOOO LONG to run, over 8 hours, if not longer....

What is the best way to delete this data???

One idea was to create a Temp Table, then move the needed data to the
temp table, then Truncate the Table, and move that data back.
I'm not very good with SQL Query Language, so can somone give me an
example on how to do this?? Or if you have a differant idea that would
be faster please let me know.

thanks,

Sam
Jul 20 '05 #1
2 7894
s0***@go.com (Sam) wrote in message news:<ed**************************@posting.google. com>...
I have a database that is 70GB big. One of the tables has over 350
million rows of data. I need to delete about 1/3 of the data in that
one table.

I was going to use a simple delete command to delete the unnessacay
data.
Something like

Delete from tablename where columname > '100'

However, it takes SOOO LONG to run, over 8 hours, if not longer....

What is the best way to delete this data???

One idea was to create a Temp Table, then move the needed data to the
temp table, then Truncate the Table, and move that data back.
I'm not very good with SQL Query Language, so can somone give me an
example on how to do this?? Or if you have a differant idea that would
be faster please let me know.

thanks,

Sam


One possible way is like this:

1. Create a new table with identical structure to the existing one
2. Insert the data you want to keep:

insert into dbo.NewTable
select col1, col2, ...
from dbo.OldTable
where ...

3. Drop the old table
4. Rename the new table:

exec sp_rename 'NewTable', 'OldTable'

This approach can be a little awkward if you have lots of keys and
constraints, so using a batch deletion is another option:

declare @rows int
set @rows = -1

set rowcount 50000 -- batch size
while @rows <> 0
begin
delete from dbo.MyTable where...
set @rows = @@rowcount
end

set rowcount 0

You can also truncate the log periodically inside the loop, if
necessary. This assumes that you don't need to be able to roll back
the deletion.

Simon
Jul 20 '05 #2
Sam (s0***@go.com) writes:
I have a database that is 70GB big. One of the tables has over 350
million rows of data. I need to delete about 1/3 of the data in that
one table.

I was going to use a simple delete command to delete the unnessacay
data.
Something like

Delete from tablename where columname > '100'

However, it takes SOOO LONG to run, over 8 hours, if not longer....

What is the best way to delete this data???

One idea was to create a Temp Table, then move the needed data to the
temp table, then Truncate the Table, and move that data back.


Since you are keeping two thirds of the data, that is likely to take
even longer time.

A better strategy is to take the operation in portions. If you are using
simple recovery, SQL Server will truncate the log between the rounds.
If you are using full or bulk-logged you have to truncate yourself.
Whichever, don't forget to take a full backup when you're done. The
significance of the log here, is that you avoid costly autogrows of
the log.

To do it portions, there are a couple of strategies.

If there is key column which determins the delete condition, you
can use this for iteration. But the simplest is probably to do:

SET ROWCOUNT 100000
WHILE 1 = 1
BEGIN
DELETE tbl WHERE ...
IF @@rowcount < 100000
BREAK
END
SET ROWCOUNT 0

Also, watch out for these things:

* Drop all indexes on the table that are not good for finding the rows
to delete. Reapply the indexes when you are done.
* If there is a trigger on the table, use ALTER TABLE DISABLE TRIGGER.
Don't forget to enable when you are done. And check that the trigger
does not perform any cascading updates or deletes.
* If the table is referenced by a foreign key in another table, make
sure that that referencing table has an index on that column.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Lars Behrens | last post by:
Hi there! For a web project I need a little expert help. I don't have written much code yet, just been fiddling around a bit, testing and planning. The web site will have a submission page for...
1
by: Andrew Arace | last post by:
I scoured the groups for some hands on code to perform the menial task of exporting table data from an Access 2000 database to Oracle database (in this case, it was oracle 8i but i'm assuming this...
5
by: Louis LeBlanc | last post by:
Hey folks. I'm new to the list, and not quite what you'd call a DB Guru, so please be patient with me. I'm afraid the lead up here is a bit verbose . . . I am working on an application that...
5
by: JL | last post by:
Hi, I was asked the following question at work: "Assume that you have 5000 columns and 5000 rows of data. You take these data from the database and need to show them in a table. Before...
3
by: kamran | last post by:
Hi, I have a web service that may return a very large amount of data. I want that data to return in chunks, like first return 10% of data than return the next 10% and so on, until all is...
10
by: Peter Duniho | last post by:
This is kind of a question about C# and kind of one about the framework. Hopefully, there's an answer in there somewhere. :) I'm curious about the status of 32-bit vs 64-bit in C# and the...
25
by: tekctrl | last post by:
Anyone: I have a simple MSAccess DB which was created from an old ASCII flatfile. It works fine except for something that just started happening. I'll enter info in a record, save the record,...
10
by: nflacco | last post by:
I'm tinkering around with a data collection system, and have come up with a very hackish way to store my data- for reference, I'm anticipating collecting at least 100 million different dataId...
3
by: MC | last post by:
I'm developing an application that needs a large (up to 4 GB) amount of temporary file space on a local (not network-attached) disk. What are some good tactics to use in a C# program to make sure...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.