SQL Server scalar function vs. subquery execution plan analysis

Question

Can anyone help me understand the SQL Server execution plan for the following queries?

I expected the subquery version (Query 2) to execute faster, because it's set-based. This appears to be the case when runnning the queries independently - marginally - however the execution plan shows the query costs as 15% vs. 85% respectively:

//-- Query 1 (15%) - Scalar Function
SELECT 
    gi.GalleryImageId,
    gi.FbUserId,
    dbo.GetGalleryImageVotesByGalleryImageId(gi.GalleryImageId) AS Votes
FROM 
    GalleryImage gi

//-- Query 2 (85%) - Subquery
SELECT 
    gi.GalleryImageId,
    gi.FbUserId,
    (SELECT COUNT(*) FROM GalleryImageVote WHERE GalleryImageId = gi.GalleryImageId)
FROM
    GalleryImage gi

What am I missing here; does the execution plan skip over the cost of the function? Also, any suggestions as to whether either of the above would be better served with a CTE or OVER/PARTITION query?

Thank you in advance!

Answer 1

Never trust the Execution Plan. It is a very useful to let you see what the plan will be, but if you want real metrics, always turn on statistics

set statistics io on
set statistics time on

..and compare actual executions. Statistics may say the expectation is 15% / 85%, but the actuals will show you what that really translates to.

There is no silver bullet to performance tuning. Even "best" queries can change over time as the shape or distribution of your data changes.

The CTE won't be much different, and I am not sure how you plan to do a PARTITION query over this, but you can try the left join form.

SELECT 
    gi.GalleryImageId,
    gi.FbUserId,
    count(v.GalleryImageId) AS Votes
FROM
    GalleryImage gi
    LEFT JOIN GalleryImageVote v ON v.GalleryImageId = gi.GalleryImageId
GROUP BY
    gi.GalleryImageId, gi.FbUserId

Answer 2

The optimiser does not know the cost of the function.

You can see the CPU and Reads and Duration via profiler though

Some related answers from similar questions. One Two

• Inline table functions expand into the main query (they are macros like views)
• Scalar (your one) and multi statement table functions do not and are black boxes to the "outer" query