Hi guys,
Welcome back mates and happy sunday!
Today an interesting post awaits us.
But first we will see what cursors are (i know, there are a couple of posts that I don't talk about theory and I would not want you to lose the habit ... haha)
This is because I have this important concept in mind:
If we really want to help SQL Server execute queries, it is very important to pay attention to using "set-based logic" rather than single-line logic.
Why updating a table by making an update for each row when making a single one involving all the rows is certainly faster?
What is a cursor
What is a cursor?
With over 20 years of experience I can't explain this concept it's an easier way:
The cursor syntax
Select id,code from orderdemo
And this Query return some data:We declare a cursor with with syntax:
DECLARE cursor_name CURSOR
FOR select_statement;
Instead of "select_statement" we write our T-SQL select statement:
DECLARE cursor_name CURSOR
FOR
Select id,code from orderdemo
;
OPEN cursor_name;
FETCH NEXT FROM
cursor_name
INTO variable_list;
DECLARE @id Integer;
DECLARE @code VarChar(20)
- A variable must be declared for each field of the query
- for clarity I have given the variable the same name as the field
WHILE @@FETCH_STATUS = 0
BEGIN
-- instruction to be executed
FETCH NEXT FROM cursor_name;
END;
CLOSE cursor_name;
DEALLOCATE
cursor_name
;
DECLARE @id Integer;
DECLARE @code VarChar(20)
DECLARE cursor_name CURSOR
FOR Select id,code from orderdemo;
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @id, @code
WHILE @@FETCH_STATUS = 0
BEGIN
-- instruction to be executed
FETCH NEXT FROM cursor_name;
END;
CLOSE cursor_name;
DEALLOCATE cursor_name;
Instead of instruction to be executed we can write any query.
For example:
DECLARE @id Integer;
DECLARE @code VarChar(20)
DECLARE cursor_name CURSOR
FOR Select id,code from orderdemo;
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @id, @code
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE orderdemo SET code = (CASE WHEN code = '01' THEN 'AA' ELSE 'BB' END) WHERE id = @id
FETCH NEXT FROM cursor_name;
END;
CLOSE cursor_name;
DEALLOCATE cursor_name;
Just select all the code and execute it:
This is the result:
From the point of view of their use now you know everything!
I remember that from the point of view of performance, performing "n" updates is less fast than performing a single massive update on all row.
But now it's time for the trick as promised
How to speed up a cursor
CREATE TABLE [dbo].[Prices](
[id] [int] identity(1,1) NOT NULL,
[idProd] [int] NULL,
[Price] [float] NULL
) ON [PRIMARY]
CREATE CLUSTERED INDEX CI_Prices_Id ON Prices([id] ASC)
CREATE INDEX IDX_Prices_IdPRod ON Prices(IdProd)
SELECT id, IdProd FROM Prices WHERE idprod = 1
And take a look to the execution plan again.
DECLARE @id Integer;
DECLARE @idprod Integer;
DECLARE cursor_name CURSOR
FOR Select id,idprod from Prices where IdProd = 1
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @id,@idprod
WHILE @@FETCH_STATUS = 0
BEGIN
-- instruction to be executed
FETCH NEXT FROM cursor_name INTO @id,@idprod
END;
CLOSE cursor_name;
DEALLOCATE cursor_name;
From the performances point of view the cost of the non clustered index is equal to 0.004 while the cost of the Key lookup is equal to 1,65: cost thousands of times higher!
Just add to the cursor the FAST_FORWARD option:
Let's run again...
DECLARE @id Integer;
DECLARE @idprod Integer;
DECLARE cursor_name CURSOR FAST_FORWARD
FOR Select id,idprod from Prices where IdProd = 1
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @id,@idprod
WHILE @@FETCH_STATUS = 0
BEGIN
-- instruction to be executed
FETCH NEXT FROM cursor_name INTO @id,@idprod
END;
CLOSE cursor_name;
DEALLOCATE cursor_name;
You just have to follow me also in the next posts!