Using alternate method to cursor

Most of the cases we use cursor, for loop like senarios, like calutating total order for an item and put the sum in a specific column, doing some work on particular table on basis of values of some other tables.

Now, cursor is fine for smaller amount of data items, but it becomes really lousy , as the number of items start to grow.

Here, i will discuss about a totally alternative to Cursor which is more faster and effective for working with huge number of data items. In my example , i will work with tables from  Sample Database called "AdventureWorks" which you can easily download from MSDN site for Sql server 2005.

Now , My objective is to Calulate the Total amount for sales item from SalesDetail Table and store it in the Sales Table (for each row).

To get this sample work , i have created  a sample Column Called TotalAmt in SalesHeader Table, there is already  a Column called TotalDue, that stores the agrregatated value of the same calculated sum that i am going to store in this TotalAmt Column, only i didnt want to mess things up for test purpose and as i want to compare my result with the main result, i decided to go for creating a separate column.

Therefore first thing  is to create the sample Column, which goes something like this.

alter table Sales.SalesOrderHeader add TotalAmt real not null default 0

Next, i have created the query the using the old cursor method that does the same work which my optimized sql will do.

declare @orderId int
declare @taxAmt real
declare @subTotal real
declare @freight real

declare adventure_cursor cursor for

Select SalesOrderID, SubTotal, TaxAmt, Freight From Sales.SalesOrderHeader

Open adventure_cursor

Fetch next From adventure_cursor

into @orderId , @subTotal, @taxAmt , @freight

while @@fetch_status = 0

begin

update Sales.SalesOrderHeader

Set

TotalAmt = @subTotal + @taxAmt + @freight

where SalesOrderID = @orderId

Fetch next From adventure_cursor

into @orderId , @subTotal, @taxAmt , @freight

end

close adventure_cursor

deallocate adventure_cursor

The query shows that , it sums the total TaxAmt, Feight and SubTotal (Total sales for quantity) and stores it in TotalAmt of  Sales.SalesOrderHeader  table for each sales order.

In the AdventureWorks Db there are around 31465 rows . It took around 19 seconds to sum and update value for each item.

Now, lets change query to my more optimized update statement solution

update Sales.SalesOrderHeader

Set TotalAmt = list.SubTotal + list.TaxAmt + list.Freight

From Sales.SalesOrderHeader as SalesOrder

inner join (

Select SalesOrderID , SubTotal, TaxAmt, Freight from

(

Select

SalesOrderID,

SubTotal,

TaxAmt,

Freight

From Sales.SalesOrderHeader

)

as T ) as list on list.SalesOrderID = SalesOrder.SalesOrderID

First of all the query does not use intermidiate varible to store data , therefore it does everything on the fly, secondly i have used  T table of sql server 2005 , which does the processing in memory  but way to faster than any Cursor query.

And, yes the query completes the opeartion in 3 seconds.Amazing!!! You must be thinking 19 seconds was not a big deal at all , but think about 3 milion rows intead of 30,000 rows , in that case surely Cursor is of no use.