Is there a way to make DELETEs faster?

Question

Is there a way to make DELETEs faster?

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

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

Arnie Rowland Mr or Mrs. 500 Points: 525 More actions · Answer 1

Often, when dropping a table and recreating, or vice versa, is not a good idea. The table may have a complex security (permissions) scheme, and when dropped, all permissions are lost.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

A truncate command is not logged, so it is very fast. But it deletes the entire table.

If you want to delete a large fraction of the table, and realize this means all indexes will need to be defragmented, another option might be to select the rows you want to keep into a temp table, truncate the original table, and copy the data back in. If your tempdb database is on a separate set of disks, this should be very fast. The table will be completely unfragmented, although nonclustered indexes may need to be rebuilt.

Another option would be to use BCP to dump the data you want to save, truncate the table, then BCP or BULK INSERT the saved data back in. It's more complicated, but if you do it correctly the export, delete, and insert operations can all run without logging. And you can control what disks the saved data is written to regardless of where tempdb is located.

If you have any foreign keys, the truncate command won't work and you're stuck with DELETE. All INSERT or BULK INSERT commands will be slower. It may be worthwhile to further complicate the above methods by dropping foreign keys before and restoring them after.

Rafferty Uy SSC-Addicted Points: 428 More actions · Answer 3

Hello there, sorry for the long reply I had to wait and ask for more details on this task I'm doing. Here's what I need:

I have one table and one view. The table needs to be able to provide data to users 100% of the time (therefore deleting, truncating won't work). New data is inserted into the table MONTHLY. When new data arrives, what happens is that we insert this new data, SWITCH the VIEW (to show the new data only), and then delete the old data.

Currently, the best option I have is still indexing. I'm not knowledgable about BCP, can I use BCP and still get the table to provide data 100% of the time? Or maybe there are other more methods?

Thanks so much!

-Rafferty

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 4

OK how bout this way to skin the cat.

NEW Table PER MONTH. Why? Glad you asked...

You only show NEW data
You modify the view monthly (I assume manually)
The NEW data arrives MONTHLY
The OLD data is discarded

This would make your life faster, easier, and much more manageable.

CREATE new table, populate it, point to it, take the old table out back and "Ole yeller it"

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 5

just to add ..

you can use Alter view . BOL : Alters a previously created view (created by executing CREATE VIEW), including indexed views, without affecting dependent stored procedures or triggers and without changing permissions.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

A truncate command is not logged, so it is very fast. But it deletes the entire table

Slightly incorrect.

TRUNCATE TABLE does not remove rows, but rather frees the data pages and remove some pointers to them. The data physically exists until it is overwritten or the db is shrunk. This fact is recorded in the logs, which is why BOL refers to TRUNCATE TABLE as a minimally logged operation. TRUNCATE can be used inside a transaction, which means that it can be rolled back. David already mentioned, that certain permission must exist in order to be able to execute this command, but I haven't read in this thread yet, that TRUNCATE TABLE will not work when the table is references by a FOREIGN KEY CONSTRAINT. You can, of course, work around this, by scripting those FK's first, DROPping them, TRUNCATE and finally recreate the FK's.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Correction to myself:

Scott, you already did mention that FOREIGN KEY thingy

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Rafferty Uy SSC-Addicted Points: 428 More actions · Answer 8

This won't work as there are foreign keys in the table so like what everyone is saying dropping or trunctating won't work. We can't also go for creating a new table because we need data to always be available to the users (and that the view is modified dynamically).

I'm now trying to see if indexing would do a considerably good job. If not, then I'll have to find a way to fix the foreign keys if the table is truncated (is this possible?)

Rafferty Uy SSC-Addicted Points: 428 More actions · Answer 9

I tried out using indexes and deleting the rows either buy chunks or not. But these methods turned out to take longer to process than using just the simple delete statement.

It seems like partitioned views + truncate is my best choice but is this possible with all the foreign key constraints?

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 10

Why can you not create the Foreign keys on a new table and then drop the old AFTER changing the view?

Again the problem is as follows

NEW Data loaded monthly
100% uptime
Access is via a VIEW
OLD data needs to get trashed

rebuttal

NEW data could be loaded into a NEW table with the correct keys, etc... built-in
I assume then you have 0% maintenance window on this system and that you have customers hitting your server during daily BACKUPS, etc... and that you don't patch your system or reboot the server..
The VIEW can easily be pointed to the new table
OLD table can be DROPPED. about a 10 second job at most...

I am probably crabby and tired so if this comes across as crass or rude I apologize. However, I think it is entirely unrealistic to believe a system is up 100% of the time, never backed up, patched, etc... and that you don't have a maintenance window to perform this load.....

<ducking in anticipation of the S@#&^$ storm a comin'

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 11

Regarding the DRI problem :

Do you upload an new set of parent data with as well the old as some new data ?

if yes :Why not only bulk-instert only the new data ? (unless the old data has been modified)

Are the old data that are not loaded again ?

What with the dependant object's rows ?

IMO you should do some sql-performance monitoring,so you are 100% sure regarding the need for 100% uptime.

It's not because your users say so, that they actualy do 24/7 usage !

I've learned that 24/7 actualy is 24/7 when the users are present. If they are not present, most of them don't need all the stuff.

Maybe a bit of monitoring can help you sort this out.