Dear all,

I'm trying to optimize a long-running query, that goes something like
this:

SELECT PUT.datefrom, LTT.CustID, PH.Ordertotal
FROM dbo.Month() PUT
CROSS JOIN #Customers LTT
LEFT JOIN Orders PH
ON PH.CustID= LTT.CustID
AND PH.DateTime >= Put.DateFrom
AND PH.DateTime < Put.DateTo
ORDER BY PUT.datefrom, LTT.CustID

When I swap the temp table with a UDF that returns the same number of
fields,
the execution plan tells me that the time to do the LEFT JOIN goes from
94% to 15%

Although this is good (faster is better), I cannot see why there is a
difference in the Clustered Index Seek ?
When I hover the cursor over the two icons, the tooptip shows the exact
same search method.
So I would expect the timings to be the same.

Here is the output of "SET STATISTICS IO ON"

Table 'ORDERS'. Scan count 5015, logical reads 15093, physical reads
314, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table '#7CD5B722'. Scan count 1, logical reads 15, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table '#4718C4EF'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

(5015 row(s) affected)
Table 'ORDERS'. Scan count 5015, logical reads 18380, physical reads 35,
read-ahead reads 279, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table '#Units____000000000377'. Scan count 1, logical reads 105,
physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table '#4718C4EF'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

dbo.Month() is a UDF that returns the first and last day of the last 5
months.

Why am I wrong here?

TIA
Henrik Staun Poulsen

MS Sql Server LazyDBA home page