Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
redbrad0
Posting Yak Master
176 Posts |
 Posted - 2005-07-21 : 21:13:40
|
| This might look like a basic question but more about performance. We currently have a DB with 4 million records in it that is geeting hit pretty hard everyday all day long. We are working on a new program to remove old information. Because of the database activity we were going to run one large SQL delete statement 2 times a day (or whatever would be best). We have two options on how to run delete and I believe option #1 would be the best to go with... What is your opinion...Option 1DELETE FROM TABLE WHERE ID=XX OR ID=XX OR ID=XX OR ID=XXOption 2DELETE FROM TABLE WHERE ID=XX;DELETE FROM TABLE WHERE ID=XX;Quality NT Web Hosting & Design |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-07-21 : 21:21:49
|
| What's the ratio of records you are keeping in the table to the ones that are staying?If most of the rows are staying, I'd say option #2 is probably the best. If most of the rows are being deleted, I'd copy the rows you want to save into a temp table, truncate the table with all the rows, and the re-insert the rows from the temp table back into the main table. Methinks this is better than deleting 4 million rows one at a time.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
redbrad0
Posting Yak Master
176 Posts |
Posted - 2005-07-21 : 22:59:37
|
| Michael,Most of the records should be staying. Well I can not say for sure as they are URL's and we will be checking to see if they are still valid URL's. The reason I was thinking that Option 1 would be better is that it would do one table scan looking for those records (well i think that is how it would work) instead of scanning the table 1000 different times.Quality NT Web Hosting & Design |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-07-21 : 23:31:22
|
| save the PK of the records to be deleted in a table, and run the queryagainst that tablesame like option1 but you don't need to hardcode the criteriasomething like:delete from table where ID in (select ID from tblDelete)HTH--------------------keeping it simple... |
|
|
|
|
|
|
Deleting Records from SQL Server - Performance