Calculate execution time of a SQL query

Question

I am providing search functionality in my website  when user searches a record then I want to display the time the query taken to get the results same as google does  When we search anything then google displays how much time it takes to get results   For this I have declared a  start variable in my SP and finding the difference in the end  as below   DECLARE  start time DATETIME  SET  start time   GETDATE    -- my query SELECT   FROM  search temp table  SELECT RTRIM CAST DATEDIFF MS   start time  GETDATE    AS CHAR 10    AS  TimeTaken    Is there any other easy and fast way  or a single line of query with which we can find out the time a query taken to execute   I m using SQL Server 2005

User · Answer

try this      DECLARE  StartTime DATETIME SET  StartTime   GETDATE      SET  EndTime   GETDATE     PRINT  StartTime       CONVERT VARCHAR 30   StartTime 121    PRINT    EndTime       CONVERT VARCHAR 30   EndTime 121    PRINT   Duration       CONVERT VARCHAR 30   EndTime - starttime 114    If that doesn t do it  then try SET STATISTICS TIME ON

User · Answer

You can use   SET STATISTICS TIME   ON   OFF     Displays the number of milliseconds required to parse  compile  and execute each statement    When SET STATISTICS TIME is ON  the time statistics for a statement are displayed  When OFF  the time statistics are not displayed  USE AdventureWorks2012    GO          SET STATISTICS TIME ON    GO   SELECT ProductID  StartDate  EndDate  StandardCost    FROM Production ProductCostHistory   WHERE StandardCost  lt  500 00    GO   SET STATISTICS TIME OFF    GO

User · Answer

I found this one more helpful and simple  DECLARE  StartTime datetime  EndTime datetime    SELECT  StartTime GETDATE    --Your Query to be run goes here--   SELECT  EndTime GETDATE      SELECT DATEDIFF ms  StartTime  EndTime  AS  Duration in milliseconds

User · Answer

declare  sttime  datetime set  sttime getdate   print  sttime Select   from ProductMaster    SELECT RTRIM CAST DATEDIFF MS   sttime  GETDATE    AS CHAR 10    AS  TimeTaken

User · Answer

Please use   -- turn on statistics IO for IO related  SET STATISTICS IO ON  GO   and for time calculation use  SET STATISTICS TIME ON GO   then It will give result for every query   In messages window near query input window

User · Answer

Well  If you really want to do it in your DB there is a more accurate way as given in MSDN   SET STATISTICS TIME ON   You can read this information from your application as well

User · Answer

Why are you doing it in SQL  Admittedly that does show a  true  query time as opposed to the query time   time taken to shuffle data each way across the network  but it s polluting your database code  I doubt that your users will care - in fact  they d probably rather include the network time  as it all contributes to the time taken for them to see the page   Why not do the timing in your web application code  Aside from anything else  that means that for cases where you don t want to do any timing  but you want to execute the same proc  you don t need to mess around with something you don t need

User · Answer

We monitor this from the application code  just to include the time required to establish close the connection and transmit data across the network   It s pretty straight-forward     Dim Duration as TimeSpan Dim StartTime as DateTime   DateTime Now   Call the database here and execute your SQL statement  Duration   DateTime Now Subtract StartTime  Console WriteLine String Format  Query took  0  seconds   Duration TotalSeconds ToString     Console ReadLine

[sql] Calculate execution time of a SQL query?

Examples related to sql

Examples related to sql-server-2005