title: CPU Time or Elapsed Time - Which actually means SQL Query's Performance?
body: <p>I have a SQL server 2012 table with 2697 Records and the table is not indexed. The data will get increased in future up to 100k records. I am not joining any other table with this one to retrieve records. Initially I created a user defined function to retrieve the records from the table. </p>
  
  <p>Later I came to know that a view will be more faster than the user defined function and hence I created a View for that table.</p>
  
  <p>TO know the Query's performance, I Included the below codes to get the CPU time and elapsed time of my UDF, VIEW and direct SQL statement. </p>
  
  <pre><code>SET STATISTICS IO ON;
  SET STATISTICS TIME ON;
  </code></pre>
  
  <p>When I pulled the data directly from my table with a select query I got the below CPU time and Elapsed time</p>
  
  <pre><code>SELECT [CollegeName]
        ,[CandidateID]
        ,[age]
        ,[race]
        ,[sex]
        ,[ethnic]
        ,[arm]
        ,[Weeknum]
        ,[siteid]
        ,[country]
        ,[Region]
        ,[SubRegion]
        ,[SNAME]
        ,[UID]
    FROM [testdata]
  </code></pre>
  
  <p>---- Result</p>
  
  <pre><code>Scan count 1, logical reads 1338, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
   SQL Server Execution Times:
     CPU time = 31 ms,  elapsed time = 4381 ms.
  </code></pre>
  
  <p>When I used the VIEW, I got the CPU time and Elapsed Time as </p>
  
  <pre><code>CREATE VIEW vw_testdata
  AS
  SELECT [CollegeName]
        ,[CandidateID]
        ,[age]
        ,[race]
        ,[sex]
        ,[ethnic]
        ,[arm]
        ,[Weeknum]
        ,[siteid]
        ,[country]
        ,[Region]
        ,[SubRegion]
        ,[SNAME]
        ,[UID]
    FROM [testdata]
  </code></pre>
  
  <p>-- Result</p>
  
  <pre><code>Scan count 1, logical reads 1324, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  SQL Server Execution Times:
         CPU time = 15 ms,  elapsed time = 5853 ms.
  </code></pre>
  
  <p>And my UDF returned as</p>
  
  <pre><code>CREATE FUNCTION [dbo].[fn_DocApproval] (@collegename nvarchar(30) = NULL)
  RETURNS TABLE 
  AS
  RETURN  
  (
  SELECT [CollegeName]
        ,[CandidateID]
        ,[age]
        ,[race]
        ,[sex]
        ,[ethnic]
        ,[arm]
        ,[Weeknum]
        ,[siteid]
        ,[country]
        ,[Region]
        ,[SubRegion]
        ,[SNAME]
        ,[UID]
    FROM [testdata] WHERE CollegeName = ISNULL(@collegename, collagename)
  )
  </code></pre>
  
  <p>-- Result </p>
  
  <pre><code>Scan count 1, logical reads 1338, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
   SQL Server Execution Times:
     CPU time = 203 ms,  elapsed time = 785 ms.
  </code></pre>
  
  <p>The UDF has very lesser elapsed time than the direct sql and the view, however the CPU time is more. </p>
  
  <p>However the CPU time is less in the view when compared to direct SQL and UDF.</p>
  
  <p>I want to know which one we need to lookout to determine the query's performance.</p>
  
  <p>Also Why does the both CPU time and elapsed time changes when I ran the same query each time?</p>
  
  <p>My Schema and sample data<a href="http://sqlfiddle.com/#!6/e6d9a" rel="noreferrer">Fiddle</a></p>
  
  <p>I have currently 2697 rows and i'm not able to load all them in fiddle.</p>