How do I determine which method I should use if
I want to optimize the performance of a database.
I took Northwind's database to run my example.
My query is I want to retrieve the Employees' First
and Last Names that sold between $100,000 and
$200,000.
First let me create a function that takes the EmployeeID
as the input parameter and returns the Employee's
First and Last name:
CREATE FUNCTION dbo.GetEmployeeName(
@EmployeeID INT
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @NAME VARCHAR(100)
SELECT @NAME = FirstName + ' ' + LastName
FROM Employees
WHERE EmployeeID = @EmployeeID
RETURN ISNULL(@NAME, '')
END
My first method to run this:
SELECT EmployeeID, dbo.GetEmployeeName(EmployeeID) AS
Employee, SUM(UnitPrice * Quantity) AS Amount
FROM Orders
JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY EmployeeID,
dbo.GetEmployeeName(EmployeeID)
HAVING SUM(UnitPrice * Quantity) BETWEEN
100000 AND 200000
It's running in 4 seconds time. And here are the
Statistics IO and Time results:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 17 ms, elapsed time = 17 ms.
(3 row(s) affected)
Table 'Order Details'. Scan count 1, logical reads 10,
physical reads 0, read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 21,
physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3844 ms, elapsed time = 3934 ms.
SQL Server Execution Times:
CPU time = 3844 ms, elapsed time = 3935 ms.
SQL Server Execution Times:
CPU time = 3844 ms, elapsed time = 3935 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Now my 2nd method:
IF (SELECT OBJECT_ID('tempdb..#temp_Orders')) IS NOT NULL
DROP TABLE #temp_Orders
GO
SELECT EmployeeID, SUM(UnitPrice * Quantity) AS Amount
INTO #temp_Orders
FROM Orders
JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY EmployeeID
HAVING SUM(UnitPrice * Quantity) BETWEEN
100000 AND 200000
GO
SELECT EmployeeID, dbo.GetEmployeeName(EmployeeID),
Amount
FROM #temp_Orders
GO
It's running in 0 seconds time. And here are the Statistics IO
and Time results:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#temp_Orders0000000000F1'. Scan count 0, logical
reads 1, physical reads 0, read-ahead reads 0.
Table 'Order Details'. Scan count 830, logical reads 1672,
physical reads 0, read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 3, physical reads 0,
read-ahead reads 0.
QL Server Execution Times:
CPU time = 15 ms, elapsed time = 19 ms.
(3 row(s) affected)
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 19 ms.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 20 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(3 row(s) affected)
Table '#temp_Orders0000000000F1'. Scan count 1,
logical reads 2, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
By the way why "SQL Server Execution Times"
exists 3 times and not just one time?
Summary:
The first code is clean, 1 single SELECT statement but
takes 4 long seconds to execute. The logical reads are
very few compared to the second method.
The second code is less clean and uses a temp table but
takes 0 second to execute. The logical reads are way
too high compared to the first method.
What am I supposed to conclude in this example?
Which method should I use over the other and why?
Are both methods good depending on which I prefer?
If I can wait four seconds, it's better to reduce the logical
reads in order to provide less Blocking on the live tables
in a heavily accessed database?
Which method should I choose on my own database?
Calling a function like dbo.GetEmployeeName gets
processed per each returned row, correct? That means
If i had a scenario where 1000 records were to be returned
would it be better to dump 1000 records to a temp table
variable and then call a function to process each record
one at a time?
Or would the direct approach without using
a temp table cause slower processing and more
blocking/deadlocks because I am calling the function
per each row as I am accessing directly from the tables?
Thank you
