How to measure the total time taken for a cursor to execute?

 Posted by Rajnilari2015 on 2/4/2016 | Category: Sql Server Interview questions | Views: 432 | Points: 40
Answer:

The below example will help to do so

DECLARE @t1 DATETIME


SELECT * INTO #temp
FROM master..spt_values

--*********** THE CURSOR BEGINS ************************
BEGIN TRAN
DECLARE @name VARCHAR
SELECT @t1 = GETDATE()

--Declare a cursor and loads the rows/data into the cursor memory
DECLARE tempCursor CURSOR
FOR SELECT name FROM #temp

-- Open the cursor
OPEN tempCursor

--Loop thru each row for processing
FETCH NEXT
FROM tempCursor
INTO @name

--Do something interesting
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #temp
SET number = 0
WHERE NAME = @name

--Repeat the process for accessing the next row available in cursor memory
FETCH NEXT FROM tempCursor
INTO @name
END

-- All operations completed.Now close the cursor
CLOSE tempCursor

-- Clean up
DEALLOCATE tempCursor

--*********** THE CURSOR ENDS ************************

--Measure the cursor processing time
SELECT
DATEDIFF(millisecond, @t1, GETDATE()) AS ElapsedTimeFromCursorInMillis;

--Clean up the table
DROP TABLE #temp


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response