Large updates and locks

Hi there,
I am looking over some code in a project I just joined and found
something that I am not sure about. The are processes which update or
delete many rows of data and the approach chosen to perform these
updates and deletes was through cursors. The setup looks something
like this:
Example:
DECLARE Orders CURSOR FOR select order_id from order where order_Date +
10 < getdate();
loop
begin
fetch orders into @.OrderId;
delete orderdetail where order_id = @.OrderId;
end;
Instead of:
delete orderdetail from orderdetail join orders where orders.order_date
+ 10 < getdate();
The motivation for it is that the locks will be more granular and other
processes will be able to access the tables instead of being locked out
for the duration of the process. The cursor approach is obviously
slower by few orders of magnitude to execute but that aside does this
approach make sense?
ThanksThis might make sense in some cases. Batching data modifications is a
common method of getting around locking issues. But whether it's necessary
or not is difficult to know based on the information you've given so far.
What percentage of the rows are being deleted each time? Is there an index
to support the deletion? Currently, your predicate (order_date + 10 <
getdate()) cannot use indexes, so the answer to the latter question is no.
You may have better luck with:
DELETE FROM orderdetail
WHERE EXISTS
(
SELECT *
FROM orders
WHERE orders.order_id = orderdetail.order_id
AND orders.order_date <= GETDATE() - 10
)
This will be able to take advantage of an index on the order_date column,
and therefore may perform better (and take more granular locks) than a
non-cursor approach using (order_date + 10).
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"boblotz2001" <boblotz2001@.yahoo.com> wrote in message
news:1124715871.853196.33820@.g44g2000cwa.googlegroups.com...
> Hi there,
> I am looking over some code in a project I just joined and found
> something that I am not sure about. The are processes which update or
> delete many rows of data and the approach chosen to perform these
> updates and deletes was through cursors. The setup looks something
> like this:
> Example:
> DECLARE Orders CURSOR FOR select order_id from order where order_Date +
> 10 < getdate();
> loop
> begin
> fetch orders into @.OrderId;
> delete orderdetail where order_id = @.OrderId;
> end;
> Instead of:
> delete orderdetail from orderdetail join orders where orders.order_date
> + 10 < getdate();
> The motivation for it is that the locks will be more granular and other
> processes will be able to access the tables instead of being locked out
> for the duration of the process. The cursor approach is obviously
> slower by few orders of magnitude to execute but that aside does this
> approach make sense?
> Thanks
>