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.