Geek Answers Handbook

SQL Server Crawl vs. Subquery Execution Plan Analysis

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

I expected the version of the subquery (Query 2) to be faster because it is set-based. It seems that this happens when executing requests independently - insignificantly - however, the execution plan shows the costs of the request as 15% versus 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 I miss here; Does the execution plan miss the cost of the function? Also, any suggestions as to whether it would be better to use one of the above with a CTE or OVER / PARTITION request?

Thank you in advance!

+6
sql-server sql-server-2008
source share
2 answers

Never trust the Execution Plan. It’s very useful to let you know what the plan will be, but if you want real indicators, always include statistics

 set statistics io on set statistics time on

.. and compare the actual performances. Statistics may say that the expected value is 15% / 85%, but the actual data will show you what it really means.

There is no silver bullet for performance tuning. Even the “best” queries may change over time as the form or distribution of your data changes.

The CTE will not differ much, and I'm not sure how you plan to make a PARTITION request on this, but you can try left join .

 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
+6
source share

The optimizer does not know the cost of the function.

You can see the processor and reading and duration through the profiler, although

Some answers to similar questions. One two

  • Embedded table functions are expanded into the main query (they are macros, such as views)
  • Scalar (your) and multi-segment table functions do not have and are black boxes for the "external" query
+4
source share

More articles:


All Articles