| |
Eliminating cursor in SQL Server
By Marin Kostadinovic
We all know how cursors are slow and how badly they tend to issue a lock on part of the table, or on the whole table, during its execution. To explain exactly what is going on, and what we can do to optimize the performance, here is an example:
Table t_Customers has 3000 records.
Table t_Orders has 1 to 100 records for each record in t_Customers table, making it a table with 65000 records.
We want to print all of the orders for customers that had orders in the last month. There are several solutions that we can use and first one is usage of the cursor:
Declare
@nCustID Int
, @cCustName varchar(30)
, @nOrdNo Int
Declare curCusOrd For
Select
C.CustID
, C.CustName
, O.OrderNo
From
t_Customers C
Left Join t_Orders O
On C.CustID = O.CustID
Where
O.OrderDate >= DateAdd( month, -1, GetDate() )
Fetch Next From curCusOrd
Into
@nCustID
, @cCustName
, @nOrdNo
While @@Fetch_Status = 0
Begin
/+
Print Order
+/
Fetch Next From curCusOrd
Into
@nCustID
, @cCustName
, @nOrdNo
End
Close curCusOrd
DeAllocate curCusOrd
In example above, cursor will lock part of each table, or both tables as a whole, while orders are printed. On top of this, a lot of disk activities will be done in order to scan both t_Customers and t_Orderd tables, resulting a very slow performance.
The first alternative is to create a temporary table, that will contain all key columns, and then to use it as a pointer instead of cursor. For a small dataset we can easily eliminate a slow performing and memory intense cursor with a temporary table. Here is a same procedure done with temporary table:
Declare
@nCustID Int
, @cCustName varchar(30)
, @nOrdNo Int
, @nRowCnt Int
, @nRows Int
Create Table #tempCustOrd
(
CusOrdID IDENTITY (1, 1) Primary key Not Null
, CustID Int Not Null
, CustName varchar(30) Not Null
, OrderNo Int
)
Insert #tempCustOrd
Select
C.CustID
, C.CustName
, O.OrderNo
From
t_Customers C
Left Join t_Orders O
On C.CustID = O.CustID
Where
O.OrderDate >= DateAdd( month, -1, GetDate() )
Select
@ nRowCnt = 1
, @nRows = Count(+)
From #tempCustOrd
While @nRowCnt = DateAdd( month, -1, GetDate() )
Select
@ nRowCnt = 1
, @nRows = Count(+)
From @t_CustOrd
While @nRowCnt
About the Author
Marin Kostadinovic is in the database development/architecture and website design business for more than 20 years. He is currently mastering Website Hosting and Development (http://www.website-hosting-development.com) and DIMM Info Systems Inc. (http://www.dimm-is.com) websites.
Article Source: http://www.simplysearch4it.com/article/35859.html
| If you wish to add the above article to your website or newsletters then please include the "Article Source: http://www.simplysearch4it.com/article/35859.html" as shown above and make it hyperlinked. |
| |
|
