SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deleting records from a huge table


Deleting records from a huge table

Author
Message
Sanz
Sanz
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 982
I have a huge table in my database. I need to delete records from this table. There are no indexes on this table. If I create an index on one of the columns the deletion process will run faster but the index creation will take a long time.

I need to know the best method to delete rows from this table.

Thanx in Advance !

Sanz
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10312 Visits: 13687
If you have enterprise edition maybe partitioning the table will work for you, else you probably want to go with a crawler delete to avoid blowing the log and reduce contention.

If you are deleting the majority of the table could be faster to insert data you want to keep into another table, drop original, then rename new table.

We really need to know the table schema, a bit of example data and the criteria you wish to delete by.

---------------------------------------------------------------------
maechismo_8514
maechismo_8514
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1836 Visits: 2228
Sanz (2/8/2010)
I have a huge table in my database. I need to delete records from this table. There are no indexes on this table. If I create an index on one of the columns the deletion process will run faster but the index creation will take a long time.

I need to know the best method to delete rows from this table.

Thanx in Advance !


How many records approx. How huge the table you are talking about? Delete chunks of data.
Sanz
Sanz
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 982
The table is 135 GB in size. There are 5 columns all of which have datatype int and varchar(15).

I would keep George's suggestion as an option (Inserting into a new table) as I will be deleting nearly 50% of the data.

Any better idea ??

Sanz
Amit Singh
Amit Singh
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 416
copying to another table would require another 135 GB on the disk ..... do we have that much space ...

you could try a BCP with queryout to output to a file (smaller in size, faster as well) with the data that you eventually want to keep.
then you truncate the table
then you BCP in the file from step 1

just some thoughts....

-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! w00t - desparately trying to fish
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7813 Visits: 25280
1. Assuming your back up is FULL RECOVERY. Perform a complete backup
2. Use Set ROWCOUNT to a resonable size (Start low, watch your log file size, and the time to process the batch, display the estimated execution plan and review it before starting your first batch)
3. At the end of each batch of deleting, backup your log file
4. Be sure to save the Full back up and all log backup, you do not want to break the chain in case you need to perform a restore.

And test, test, and test again before using on your Production DB.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
SQLBOT
SQLBOT
SSC Eights!
SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)

Group: General Forum Members
Points: 948 Visits: 836
I would create the index on the columns in your delete statement's where clause.
It won't take that long if the disk is fairly fast and your delete will be far far faster.

If you have enterprise edition you can create the index with online=on.

If you do go that route, make sure to calculate the space the index will consume when you add it.

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
SQLBOT
SQLBOT
SSC Eights!
SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)

Group: General Forum Members
Points: 948 Visits: 836
I would create the index on the columns in your delete statement's where clause.
It won't take that long if the disk is fairly fast and your delete will be far far faster.

If you have enterprise edition you can create the index with online=on.

If you do go that route, make sure to calculate the space the index will consume when you add it.

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85923 Visits: 41091
Amit Singh (2/8/2010)
copying to another table would require another 135 GB on the disk ..... do we have that much space ...

you could try a BCP with queryout to output to a file (smaller in size, faster as well) with the data that you eventually want to keep.
then you truncate the table
then you BCP in the file from step 1

just some thoughts....


That would actually take more disk space because the data would materialize as characters instead of datatypes.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85923 Visits: 41091
Sanz (2/8/2010)
I have a huge table in my database. I need to delete records from this table. There are no indexes on this table. If I create an index on one of the columns the deletion process will run faster but the index creation will take a long time.

I need to know the best method to delete rows from this table.

Thanx in Advance !


Do you have a primary key on this table? I'm asking because a lot of folks don't know that a Primary Key also makes an index.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search