Query Optimization: CPU speed or Logical Reads better?

[serge]

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.GetEmployee Name(
@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.GetEmployee Name(EmployeeID ) AS
Employee, SUM(UnitPrice * Quantity) AS Amount
FROM Orders
JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY EmployeeID,
dbo.GetEmployee Name(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('temp db..#temp_Order s')) 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.GetEmployee Name(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_Orders00 00000000F1'. 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_Orders00 00000000F1'. 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.GetEmployee Name 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

[Doug]

"My query is I want to retrieve the Employees' First
and Last Names that sold between $100,000 and
$200,000. "

Then why don't you just write a select statement that does
this????????? Perhaps a better example is needed?