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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With