Delete large number of records

Question

Post reply

Delete large number of records

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

oneteabag SSC Eights! Points: 954 More actions · Answer 1

Lynn,

I have done table partitioning in the past. Can you point me to a sample to follow.?

Thanks a lot.

oneteabag SSC Eights! Points: 954 More actions · Answer 2

Does anyone has an example to follow close to what I am trying to do (create a partition on trx_date)?

Thanks to all.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 3

Why do you refuse to create a clustered index??

Since you have Enterprise Edition, you can create the index online so it will barely, if at all, affect other users.

Drop all the nonclustered indexes except on the tr date, then issue the create clustered index command:

CREATE CLUSTERED INDEX ... WITH ( FILLFACTOR = ..., ONLINE = ON, SORT_IN_TEMPDB = ON, ... )

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

ScottPletcher (9/9/2015)
Why do you refuse to create a clustered index??
Since you have Enterprise Edition, you can create the index online so it will barely, if at all, affect other users.
Drop all the nonclustered indexes except on the tr date, then issue the create clustered index command:
CREATE CLUSTERED INDEX ... WITH ( FILLFACTOR = ..., ONLINE = ON, SORT_IN_TEMPDB = ON, ... )

The OP will need to create the clustered index if he partitions the table. Switching in and out partitions will make the job of adding new months in and taking old months out much easier.

To the OP, I don't have anything I can point you at other than Books Online. Maybe others have more they may be able to share in this area. I have used the partition aspect of SQL to help delete large amounts of data and that is about it since we are only using Standard Edition of SQL Server. We don't have any partitioned tables.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 5

I have to agree with Scott on this one. This table is actually fairly small and, because of the monthly rolloff, isn't going to get much bigger quickly. If a Clustered Index were put on Trx_Date, there'd be virtually no reason to suffer the pains of partitioning.

I also agree with Scott on the non-clustered indexes. It looks like someone may have followed the old idea of adding one index to each column that could ever be used in a WHERE clause and that's a really bad idea especially since all those indexes have to be deleted from on every delete and inserted to on every insert. Like Scott, I'd drop all the NCIs and then let SQL Server suggest indexes through missing indexes and then do my best to consolidate those recommendations or ignore some of them.

If you want the month end inserts and deletes to go much faster, disable all (hopefully new) NCIs before the monthly deletes or inserts and then rebuild them. You'd need to do that anyway so use it to your advantage.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100950 More actions · Answer 6

Jeff Moden (9/9/2015)
It looks like someone may have followed the old idea of adding one index to each column that could ever be used in a WHERE clause and that's a really bad idea especially since all those indexes have to be deleted from on every delete and inserted to on every insert.

True. But it's likely an even worse idea because the vast majority of them will never even be used anyway. All the overhead with no gain.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 7

ScottPletcher (9/9/2015)
Jeff Moden (9/9/2015)
It looks like someone may have followed the old idea of adding one index to each column that could ever be used in a WHERE clause and that's a really bad idea especially since all those indexes have to be deleted from on every delete and inserted to on every insert.
True. But it's likely an even worse idea because the vast majority of them will never even be used anyway. All the overhead with no gain.

Exactly.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

oneteabag SSC Eights! Points: 954 More actions · Answer 8

Thanks everyone, I will create CI and see how it goes.

Appreciate everybody opinion.

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 9

oneteabag (9/8/2015)
A New Monthly data is being loaded, checked and finally approved after 6 or 7 iteration before approval.
Because of this iteration the monthly data set is being added then deleted then added then deleted few times.
Because the table is big this process takes time, any thoughts on how to make the delete insert process faster.

If you are not able to adapt the code this is probably of no use, but how about UpSert the changes rather than a full DELETE / re-INSERT?

With a suitable WHERE clause comparing all old/new columns there may only be a modest number of rows that are actually physically changed?

oneteabag SSC Eights! Points: 954 More actions · Answer 10

Do you have an example for upSert, Not sure if I understand your solution.

Thanks

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 11

oneteabag (9/17/2015)
Do you have an example for upSert, Not sure if I understand your solution.

Update any rows that already exist, Insert any that are new.