Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Deleting records from a huge table Expand / Collapse
Author
Message
Posted Monday, February 8, 2010 7:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:25 AM
Points: 184, Visits: 852
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
Post #861638
Posted Monday, February 8, 2010 7:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:35 AM
Points: 5,992, Visits: 12,940
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.


---------------------------------------------------------------------

Post #861656
Posted Monday, February 8, 2010 10:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
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.
Post #861883
Posted Monday, February 8, 2010 10:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:25 AM
Points: 184, Visits: 852
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
Post #861891
Posted Monday, February 8, 2010 12:27 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:57 PM
Points: 110, Visits: 381
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 !! - desparately trying to fish
Post #861966
Posted Monday, February 8, 2010 4:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 5,618, Visits: 25,235
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
Post #862125
Posted Monday, February 8, 2010 6:26 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, 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
Post #862152
Posted Monday, February 8, 2010 6:27 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, 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
Post #862153
Posted Monday, February 8, 2010 9:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #862185
Posted Monday, February 8, 2010 9:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #862186
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse