In this article, we will test the performance of the cursors with the help of the client statistics that shows the different statistics like Network statistics, Time statistics, Query Profile Statistics.
Step#1: First reset client statistics by click on Query >> Reset client Statistics. This will clear the client statistics
Step#2: Click to add the client statistics from the include client statistics(short cut: Shift + Alt + S). It will add the client statistics for the each query executions
Step#3: Use below sample code to test the cursor and performance of the different cursors
CREATE TABLE #tempcursorperformancetest
(
id INT,
NAME VARCHAR(max),
)
- Insert some values in the temp table:
INSERT INTO #tempcursorperformancetest VALUES (1,‘Mahesh’); INSERT INTO #tempcursorperformancetest VALUES (2, NULL); INSERT INTO #tempcursorperformancetest VALUES (3,‘Madhuri’); INSERT INTO #tempcursorperformancetest VALUES (4,‘Nilesh’); INSERT INTO #tempcursorperformancetest VALUES (5,‘Gauri’); INSERT INTO #tempcursorperformancetest VALUES (6, NULL ); INSERT INTO #tempcursorperformancetest VALUES (7,‘Nitin’); INSERT INTO #tempcursorperformancetest VALUES (8,‘Mangesh’);
- Check values of the temp table using below query
SELECT *
FROM #tempcursorperformancetest
–Execution 1:
DECLARE @id INT,
@Name NVARCHAR(max),
@JoinName NVARCHAR(max),
@Mycursor CURSOR
SET @Mycursor = CURSOR
FOR SELECT NAME
FROM #tempcursorperformancetest
OPEN @Mycursor;
FETCH next FROM @Mycursor INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @JoinName = ‘ ‘ + @JoinName + ‘ ‘ + @Name
IF @JoinName IS NULL
BEGIN
SET @JoinName = ‘ ‘
END
FETCH next FROM @Mycursor INTO @Name
END
CLOSE @Mycursor
DEALLOCATE @Mycursor
SELECT @JoinName
–Execution 2: LOCAL FAST_FORWARD
DECLARE @id INT,
@Name NVARCHAR(max),
@JoinName NVARCHAR(max),
@Mycursor CURSOR
SET @Mycursor = CURSOR local fast_forward
FOR SELECT NAME
FROM #tempcursorperformancetest
OPEN @Mycursor;
FETCH next FROM @Mycursor INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @JoinName = ‘ ‘ + @JoinName + ‘ ‘ + @Name
IF @JoinName IS NULL
BEGIN
SET @JoinName = ‘ ‘
END
FETCH next FROM @Mycursor INTO @Name
END
CLOSE @Mycursor
DEALLOCATE @Mycursor
SELECT @JoinName
— Execution 3: FAST_FORWARD
DECLARE @id INT,
@Name NVARCHAR(max),
@JoinName NVARCHAR(max),
@Mycursor CURSOR
SET @Mycursor = CURSOR fast_forward
FOR SELECT NAME
FROM #tempcursorperformancetest
OPEN @Mycursor;
FETCH next FROM @Mycursor INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @JoinName = ‘ ‘ + @JoinName + ‘ ‘ + @Name
IF @JoinName IS NULL
BEGIN
SET @JoinName = ‘ ‘
END
FETCH next FROM @Mycursor INTO @Name
END
CLOSE @Mycursor
DEALLOCATE @Mycursor
SELECT @JoinName
DROP TABLE #tempcursorperformancetest
Trail#1: Normal Cursor
Trail#2: Local_Fast_Forward
Trail#3: Fast_Forward
Statistics observed while executing above cursors. you will see the different parameters where the performance of the cursors differs like Network statistics, Time statistics, Query Profile Statistics.
