SQL Server scalar function vs. subquery execution plan analysis
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 WHER开发者_如何学运维E 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!
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
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
精彩评论