19

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.

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.

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. 

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

When I pulled the data directly from my table with a select query I got the below CPU time and Elapsed time

SELECT [CollegeName]
      ,[CandidateID]
      ,[age]
      ,[race]
      ,[sex]
      ,[ethnic]
      ,[arm]
      ,[Weeknum]
      ,[siteid]
      ,[country]
      ,[Region]
      ,[SubRegion]
      ,[SNAME]
      ,[UID]
  FROM [testdata]

---- Result

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.

When I used the VIEW, I got the CPU time and Elapsed Time as 

CREATE VIEW vw_testdata
AS
SELECT [CollegeName]
      ,[CandidateID]
      ,[age]
      ,[race]
      ,[sex]
      ,[ethnic]
      ,[arm]
      ,[Weeknum]
      ,[siteid]
      ,[country]
      ,[Region]
      ,[SubRegion]
      ,[SNAME]
      ,[UID]
  FROM [testdata]

-- Result

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.

And my UDF returned as

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)
)

-- Result 

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.

The UDF has very lesser elapsed time than the direct sql and the view, however the CPU time is more.

However the CPU time is less in the view when compared to direct SQL and UDF.

I want to know which one we need to lookout to determine the query's performance.

Also Why does the both CPU time and elapsed time changes when I ran the same query each time?

My Schema and sample dataFiddle

I have currently 2697 rows and i'm not able to load all them in fiddle.

Karthik Venkatraman
  • 1,619
  • 4
  • 25
  • 55
  • Without the actual code & table definition (+maybe the data) it's really not possible to say anything specific. Query plans might help too. Multi statement UDFs are really bad when joined with other tables, so if possible, try to stay away from those. Inline UDFs should be ok – James Z Dec 11 '15 at 17:58
  • @JamesZ -- Added the schema as per your request. – Karthik Venkatraman Dec 13 '15 at 16:33
  • Hi All, any solutions to my answer.... – Karthik Venkatraman Dec 23 '15 at 05:44
  • CPU time means it takes more CPU and I/O is storage speed. Both are of course important and you should know which is more critical in your system. The `isnull` in your UDF is most likely the reason for it using more CPU. You should look into indexing. – James Z Dec 23 '15 at 10:14
  • I have the indexing done in my tables. – Karthik Venkatraman Dec 23 '15 at 10:43

2 Answers2

23

As per the article SQL Query performance Tuning

SQL Server parse and Compile time : When we submit a query to SQL server to execute,it has to parse and compile for any syntax error and optimizer has to produce the optimal plan for the execution. SQL Server parse and Compile time refers to the time taken to complete this pre -execute steps.If you look into the output of second execution, the CPU time and elapsed time are 0 in the SQL Server parse and Compile time section. That shows that SQL server did not spend any time in parsing and compiling the query as the execution plan was readily available in the cache. CPU time refers to the actual time spend on CPU and elapsed time refers to the total time taken for the completion of the parse and compile. The difference between the CPU time and elapsed time might wait time in the queue to get the CPU cycle or it was waiting for the IO completion. This does not have much significance in performance tuning as the value will vary from execution to execution. If you are getting consistent value in this section, probably you will be running the procedure with recompile option.

SQL Server Execution Time: This refers to the time taken by SQL server to complete the execution of the compiled plan. CPU time refers to the actual time spend on CPU where as the elapsed time is the total time to complete the execution which includes signal wait time, wait time to complete the IO operation and time taken to transfer the output to the client.The CPU time can be used to baseline the performance tuning. This value will not vary much from execution to execution unless you modify the query or data. The load on the server will not impact much on this value. Please note that time shown is in milliseconds. The value of CPU time might vary from execution to execution for the same query with same data but it will be only in 100's which is only part of a second. The elapsed time will depend on many factor, like load on the server, IO load ,network bandwidth between server and client. So always use the CPU time as baseline while doing the performance tuning.

The lesser number of logical reads you have in the plan, the more efficient is the query.

  • 1
    Why would the elapsed time ever be less than the CPU time? I have this for my query right now: `SQL Server parse and compile time: CPU time = 31 ms, elapsed time = 39 ms. (177347 rows affected) SQL Server Execution Times: CPU time = 75016 ms, elapsed time = 62991 ms. ` – Reversed Engineer Jul 30 '18 at 13:52
  • There was parallelism involved here, and the CPU time indicates time spent on all CPUs vs the elapsed time which indicates total wall time spent between the start & end of the query. – Jordan Parker Jul 31 '20 at 01:32
4

If you are using a modern server, always look at "elapsed time" not on "CPU time". In the era of fast multi-core processors, multiprocessor boards and so on - all other factors conditioning a quick response, not a processor, are important. It happens that with complicated queries, the indication of CPU time is 5 times greater than the total time (check "execution plan" - then there will be many parallelisms).

Maciej Niemir
  • 604
  • 6
  • 8