Monday, March 8, 2010

Using "quirky" updates to develop well performing set based solutions

This blog is to illustrate a scenario where 'quirky' update can be used efficiently to provide an optimized set based solution.

Recently I was asked by one of guys in a forum to provide an optimized solution for the below scenario.

Consider the case of Order and Despatch system where Items are ordered and then get despatched from despatch section. The requirement was to consolidate the despatch quantity against Orders raised. The solution involved aggregating total despatched quantity for each item code and consolidating them against outstanding orders in the sequence of ordering based on order date.

The usual way to deal with this is to use two cursors to loop through each of item codes, accumulating the despatch counts and then allocating them against available orders in desired order ie. sequence of order date. The solution will be as below

The problem with this approach is use of two cursors which does the row by row processing and can be quite slow.

Now lets see how we can approach the same problem using quirky update.

Quirky update

--------------------

Quirky update works on basis of clustered index available on table. By using UPDATE SET construct it updates each row in table and at same time sets the next values for variables in iteration.It continues this until it updates all rows in table

As we see from above we get same output. The advantage here is that we have dispensed with two cursors and replaced them with single set based update. Thus quirky updates proves to be really helpful in situations like this to provide us with set based solution.

A few things to note in above query are

1. We need a clustered index on columns based on which we want operation to take place sequentially

2. Better to use TABLOCKX to place a single exclusive lock on table

3. MAXDOP is set to 1 to avoid parallelism

4. The assignment statements to set clustered indexed columns are needed to remember current row values for proceeding with next row each time

Thus by means of applying quirky update we were able to get a more optimized solution