calculating query cost

I was trying to calculate the difference between set-based solution vs
row-by-row solution by looking at the execution plan.
For my row-by-row solution, I have the following (i won't show you the set
based since it is irrelvant here)
SELECT Vendor_ID, (SELECT TOP 1 CONVERT(VARCHAR, Note_Enter_Date, 101) + ' '
+ Note FROM tblNotes WHERE Contact_Type = 1 AND Contact_ID = Vendor_ID ORDER
BY Note_ID DESC) AS Note
FROM tblVendors
WHERE Vendor_Status = 6
As soon as I put the logic (SELECT TOP 1 CONVERT(VARCHAR, Note_Enter_Date,
101) + ' ' + Note FROM tblNotes WHERE Contact_Type = 1 AND Contact_ID =
Vendor_ID ORDER BY Note_ID DESC) in user defined function, query costs are
different? Why is that? Does the optimizer ignore user define function
cost when displaying query cost?
SELECT Vendor_ID, Note = dbo.udf_GetLatestNote(1, Vendor_ID)
FROM tblVendors
WHERE Vendor_Status = 6Hi Justin,
Estimated costs of two query rewrites (or two different queries) are not
always comparable and they shouldn't be used this way.
Only estimated costs of different plans for the same query are comparable,
and they should correlate well with execution time. The primary use of
execution cost is to help the query optimizer rank different plan
alternatives for a query, but not to exactly model the execution time.
Therefore there are things that are not captured in the optimizer's cost
model.
Related to your two query rewrites; in the one with the UDF, the UDF is
compiled separately (you should see two execution plans) and the optimizer
cannot optimize across the UDF boundary or in-line the SELECT statement
inside the UDF (this is something that might be possible in a future version
of SQL Server). Therefore, the query without the UDF has better chances for
a more efficient execution.
Regards,
Leo
"Justin" <nospam@.nospam.com> wrote in message
news:%23KJC%23$vjGHA.4212@.TK2MSFTNGP03.phx.gbl...
>I was trying to calculate the difference between set-based solution vs
>row-by-row solution by looking at the execution plan.
> For my row-by-row solution, I have the following (i won't show you the set
> based since it is irrelvant here)
> SELECT Vendor_ID, (SELECT TOP 1 CONVERT(VARCHAR, Note_Enter_Date, 101) + '
> ' + Note FROM tblNotes WHERE Contact_Type = 1 AND Contact_ID = Vendor_ID
> ORDER BY Note_ID DESC) AS Note
> FROM tblVendors
> WHERE Vendor_Status = 6
> As soon as I put the logic (SELECT TOP 1 CONVERT(VARCHAR, Note_Enter_Date,
> 101) + ' ' + Note FROM tblNotes WHERE Contact_Type = 1 AND Contact_ID =
> Vendor_ID ORDER BY Note_ID DESC) in user defined function, query costs are
> different? Why is that? Does the optimizer ignore user define function
> cost when displaying query cost?
> SELECT Vendor_ID, Note = dbo.udf_GetLatestNote(1, Vendor_ID)
> FROM tblVendors
> WHERE Vendor_Status = 6
>