Grant Fritchey (3/28/2014)
You're posting in the 2008 forum.Not sure what you mean by batch delete. SET ROWCOUNT just limits the rows returned, but the query plan and the access of the data isn't modified by ROWCOUNT. So, if you want to actually limit the number of rows, it's better to use additional filtering to reduce the number of rows being accessed so that the optimizer can actually do something different with the execution plan.
Then what is best way to delete bulk of Records in the table with minimal time and minimal locking.
Atucally i ma having fkey reference in 10 tables , when i try to delete some 200000 records from my main table whcih is having 1/2 million records it is taking more then 30 min to delete. (nornal delete not the batch delete, batch delete takes 40 min to delete)
Thats why i had an example of same to delete a table without any Fkey reference, it took only 1 min to delete.
So my question is Fkey is blocking the delete operation? if so how to overcum this? droping and recreating Fkey is the best way to do in production?