Write a program in SQL Server to measure the time consumed to execute the query.

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

The below program will do so

DECLARE @t1 DATETIME;

DECLARE @t2 DATETIME;
SET @t1 = GETDATE();

--*********** THE QUERY BEGINS ************************

DECLARE @maxLimit INT = 1000000

;WITH
a AS (SELECT 1 AS i UNION ALL SELECT 1),
b AS (SELECT 1 AS i FROM a AS x, a AS y),
c AS (SELECT 1 AS i FROM b AS x, b AS y),
d AS (SELECT 1 AS i FROM c AS x, c AS y),
e AS (SELECT 1 AS i FROM d AS x, d AS y),
f AS (SELECT 1 AS i FROM e AS x, e AS y),
NumCTE AS (SELECT TOP(@maxLimit) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
FROM f)
SELECT *
FROM NumCTE

--*********** THE QUERY ENDS ************************

SET @t2 = GETDATE();
SELECT
DATEDIFF(millisecond,@t1,@t2) AS ElapsedTimeInMilliSeconds,
(DATEDIFF(millisecond,@t1,@t2) - (((DATEDIFF(millisecond,@t1,@t2))/60000)*60000)) /1000 AS ElapsedTimeInSeconds;

/* Result */
ElapsedTimeInMilliSeconds ElapsedTimeInSeconds
5246 5.246


We declared two variables viz @t1 and @t2 as DATETIME. @t1 is set to current date and time. Then we wrote the SQL Query. Then we set the Date and Time component for @t2. And finally we performed a difference between the two dates and their times for obtaining the Difference in time both the seconds and milliseconds.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response