CPU time or Elapsed time - what does SQL Query Performance really mean?

Question

I have a SQL Server 2012 table with 2697 records and the table is not indexed. In the future, data will increase to 100 thousand records. I do not join any other table to get entries. First, I created a custom function to retrieve records from a table.

Later I found out that the view will be faster than the user-defined function, and so I created a view for this table.

To find out the performance of the query, I included the codes below to get the processor 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 using the select query, I got 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 VIEW, I got 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 is back 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. 

UDF has much less past time than direct sql and view, however CPU time is longer.

However, processor time is shorter compared to direct SQL and UDF.

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

Also, why does the time and time of the processor and elapsed time change each time the same request is started?

My schema and Fiddle data examples

I have 2697 lines, and I can’t load them all in a script.

Answer 1

According to Tuning SQL Query Performance

SQL Server analysis and compilation time: when we send a query to the SQL server for execution, it should analyze and compile any syntax error, and the optimizer should create an optimal plan for execution. SQL Server analysis and compilation time refers to the time taken to complete these preliminary steps. If you look at the result of the second run, the CPU time and elapsed time are 0 in the SQL Server parse and Compile time section. This shows that the SQL server did not waste time parsing and compiling the query, because the execution plan was easily accessible in the cache. CPU time refers to the actual time spent on the CPU, and elapsed time refers to the total time taken to complete the analysis and compilation. The difference between the processor time and the elapsed time may wait in the queue to get the processor cycle, or it was waiting for the I / O to complete. This is not a big deal when tuning performance, because the value will vary from execution to execution. If you get a consistent value in this section, you might be performing a procedure with the option of recompiling.

SQL Server Runtime: This refers to the time taken by the SQL server to complete the execution of the compiled plan. CPU time refers to the actual time spent on the CPU, where, after the time has passed, this is the total time to complete the execution, which includes the time to wait for the signal, the time to wait for the I / O operation to complete, and the time taken to transmit the output to the client. CPU time can be used for basic performance tuning. This value will not differ much from execution to execution, unless you change the query or data. Server load will not greatly affect this value. Please note that the time shown is in milliseconds. The processor time value can vary from execution to execution for the same request with the same data, but it will be only 100, which is only part of a second. The elapsed time will depend on many factors, such as loading on the server, loading I / O, network bandwidth between the server and the client. Therefore, always use the CPU time as the baseline when tuning performance.

The smaller the number of logical readings contained in the plan, the more efficient the request.