Cursor

Source:Refer  the Source for details.

Cursors in sql server Part 63

by kudvenkat

Relational Database Management Systems, including Sql Server are very good at handling data in SETS. For example, the following "UPDATE" query, Updates a set of rows that matches the condition in the "WHERE" clause at the same time.

Update tblProductSales Set  UnitPrice = 50 where ProductId = 101

tblProducts:

Id         Name                                Description

1          Product - 1                         Product - 1 Description

2          Product - 2                         Product - 2 Description

3          Product - 3                         Product - 3 Description

4          Product - 4                         Product - 4 Description     

5          Product - 5                         Product - 5 Description      

tblProductsSales:

Id        ProductId                 UnitPrice        QuantitySold

1          5                               5                     3

2          4                               23                   4 

3          3                               31                   2 

4          4                               93                   9

5          5                               72                   5

However, if there is ever a need to process the rows , on a row-by-row basis, then cursors are your

choice.Cursors are very bad for performance and should be avoided always.Most of the time, cursors can be very easily replaced using joins.

Cursors in Sql Server:

There are different types of cursors in sql server as listed below. We will talk about the differences

between these cursor types in a later video session.

1.Forward only

2.Static

3.Keyset

4.Dynamic

DEMO

The cursor will loop through each row in tblProductSales table.As there are 600,000 rows, to be processed on a row-by-row basis, it takes around 40 to 45 seconds on my machine.We can achieve

this very easily using a join, and this will significantly increase the performance.We will discuss about this in our next video session.

Note:

Cursor is nothing but a pointer to a row.

Select top 10 * from tblProducts

Select top 10 * from tblProductsSales

Select count(*) from tblProducts

Select count(*) from tblProductssales

Select Name, UnitPrice FROM tblProducts join tblProductSales on tblProducts.Id=tblProductSales.ProductId

Where ( Name = 'Product - 55'   or  Name = 'Product - 65' or Name like 'Product - 100%)

---

Declare @ProductId  int

Declare @Name nVarchar(30)

Declare ProductCursor CURSOR FOR

Select Id,Name from tblProducts where Id<=1000

Open ProductCursor

Fetch Next from ProductCursor into  @ProductId , @Name

While (@@ FETCH_STATUS = 0)

Begin

Print 'Id=' + Cast(@ ProductId as NVarchar(10) ) + 'Name' + @Name

End

CLOSE  ProductCursor

DEALLOCATE ProductCursor

--
Declare @ProductName  nVarchar(50)

Select @ProductName = Name from  tblProducts where  Id= @ProductId

if (@ProductName = 'Product - 55')

Begin

Update  tblProductSales Set UnitPrice=55 Where ProductId = @ProductId

End 

else if  ( @ProductName = 'Product - 65')

Begin

Update tblProductSales Set UnitPrice = 65 Where ProductId = @ProductId

End

else if (@ProductName like 'Product - 100%')

Begin

Update tblProductSales Set UnitPrice=1000 Where ProductId=@ProductId

End 

--
Declare @ProductId int
Declare @ProductName nVarchar(30)

Declare ProductCursor CURSOR FOR

Select ProductId From  tblProductSales 

Open ProductCursor

Fetch  Next  FROM  ProductCursor into @ProductId

While (@@FETCH_STATUS = 0 )
Begin

Select @ProductName=Name from tblProducts Where Id=@ProductId

if (@ProductName = 'Product - 55')

Begin

Update  tblProductSales Set UnitPrice=55 Where ProductId = @ProductId

End 

else if  ( @ProductName = 'Product - 65')

Begin

Update tblProductSales Set UnitPrice = 65 Where ProductId = @ProductId

End

else if (@ProductName like 'Product - 100%')

Begin

Update tblProductSales Set UnitPrice=1000 Where ProductId=@ProductId

End 

 Fetch Next from ProductCursor into  @ProductId 

End

CLOSE  ProductCursor

DEALLOCATE ProductCursor

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

Source:Replacing cursors using joins in sql server Part 64

In Part 63, We have discussed about cursors. The example, in Part 63, took around 45 seconds in my machine. Please watch Part 63, before proceeding with this video. In this video will re-write the

example, using a join.

Update tblProductSales

Set UnitPrice = 

  Case

    When Name='Product - 55' Then 55

    When Name='Product - 65' Then 65

    When Name='Product - 100%' Then 1000

Else

    UnitPrice

End Case

From tblProductSales

join    tblProducts

On tblProducts.Id=tblProductSales.ProductId Where ( Name='Product - 55' or Name='Product - 65' or Name like 'Product - 100%' 

When i executed this query, on my machine it took less than a second, where as the same thing

using cursor took 45 seconds.

Just  imagine the amount of impact cursors have on performance.Cursors should be used as your last option.

Most of  the time Cursors can be very easily replaced using joins.

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