6

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!

Robarondaz
  • 555
  • 5
  • 12
  • Did you have them both in the same query editor window? Because the query analyzer will compare them and assign each a relative value to what is in the window (15%/85%). – JNK Jan 20 '11 at 17:48
  • Yes, that's where the % values came from :) – Robarondaz Jan 21 '11 at 09:23
  • Query execution time doesn't always equal to the cost. SQL Server seems to weight CPU and RAM usage higher than HDD reads. – jahu Aug 19 '15 at 11:33

2 Answers2

6

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
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • Cheers, I didn't know about statistics. Actual execution time was 265ms (Query 1) vs 2ms (Query 2); much more in line with what I originally expected! – Robarondaz Jan 21 '11 at 09:21
4

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
Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676