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 |
|
alejo46
Posting Yak Master
157 Posts |
 Posted - 2012-07-04 : 21:14:54
|
Good eveningI've got a simple deletion like this:delete from tablexwhere start_date >= '20120101'and start_date < = '20120131'i known optimization depends from some factors like index, the table size and so on, but this script initially was implemented in batch using the set rows ....I've been said that executing the deletion directly (no batch) is faster than in batch, its true or false ? and why ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-04 : 23:56:32
|
| it depends on the size of dataset. how big is your data as returned by above query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2012-07-05 : 11:17:44
|
| it must return about 67 millions of rows affected |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-05 : 11:42:30
|
| do you've too many indexes on table? also is table partitioned?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2012-07-05 : 12:30:57
|
| the table is not partitionated, and this table have some indexes and the start_date field has index too. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-05 : 12:35:33
|
| see estimated execution plan and identify costly steps in deletion query.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-05 : 13:13:25
|
| You're trying to delete 67 million rows, how many rows do you need to keep in the table? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
optimize deletion in sql server 2000