Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 6000 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 1000 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

ID:93586
Title:Are SQL Server subqueries bad? Let's find out!
URL:http://dynamicsgpland.blogspot.com/2018/01/are-sql-server-subqueries-bad-lets-find.html
Description:By Steve Endow

For the past several years, I've noticed that I have generally avoided using subqueries, based on a suspicion that they are probably less efficient than a JOIN.  I do still use them for one-time or ad-hoc queries where performance isn't a concern, but I have been avoiding them for any production queries that I release.  But I haven't done any research to support my suspicion.

This Saturday morning, while working on another SQL query optimization issue, I figured I would try a test to compare the performance of a simple subquery vs. a JOIN.

What do you think?  Do you think that subqueries are typically slower than JOINs?  If so, how much slower?

Here's a YouTube video where I test my sample queries and review the results.



Before doing my test, I searched the interwebs and I found a post (unfortunately the images aren't showing for me) that appears to definitively demonstrate the correlated subqueries perform much worse than similar JOIN queries.  The explanation made sense.

To qualify my test setup: These queries were run on SQL Server 2014 SP1 using Management Studio 17.4 against the GP 2016 Fabrikam/TWO database. And also notable is that I have 73,752 records in my SOP30300 table--which is probably quite a bit more than a vanilla TWO database.  I suspect this is important, as the results may be different for other SQL Server versions, and may vary based on the number of tables and records.




Here is my very simple sample subquery, which I believe is technically called a "correllated subquery", because it uses a value from the main query to filter the results of the subquery. (or the inner query uses a value from the outer query)

The second query produces the same result by using a JOIN.


SET STATISTICS IO, TIME ON

SELECT i.ITEMNMBR AS ItemNumber, (SELECT COUNT(*) FROM dbo.SOP30300 WHERE ITEMNMBR = i.ITEMNMBR) AS LineCount FROM IV00101 i

SELECT i.ITEMNMBR, COUNT(sop.ITEMNMBR) AS LineCount
FROM IV00101 i
LEFT OUTER JOIN SOP30300 sop ON sop.ITEMNMBR = i.ITEMNMBR
GROUP BY i.ITEMNMBR

SET STATISTICS IO, TIME OFF


Pretty simple--it lists all item numbers, and for each item, counts how many SOP lines exist for that item.

My assumption was that the subquery would be noticeably slower than the JOIN.

To my surprise, the two queries had effectively the same cost and performance.


Interestingly, there are more reads for the subquery version, but the performance is effectively the same--in fact the subquery was slightly faster in this test.  This is not what I expected.

But there's more!

Take a look at the actual execution plan.  Take a good, close look.  What do you see?



I am sure that the first few times I ran the two queries, there WAS a difference in the execution plan, but it still showed a 50%/50% for the two queries--effectively the same cost. But after several runs, I now consistently see the exact same query plan for both queries.  The EXACT same query plan.

WHAT?  This is absolutely not what I expected.

And after running the query a few more times, the statistics are now always identical.  I'm afraid the machines are learning.



So...what does this mean?  Well, I wouldn't call this a universal rule, but, for a simple query, on SQL Server 2014 SP1, with a relatively small result set, it appears that SQL Server is able to figure out that the two different queries are effectively the same, and after running the queries a few times, SQL uses the exact same execution plan for both queries.

Does this mean that subqueries are exactly the same as JOINs?  I would assume the answer is no. If you have a more complex query, a slightly different subquery, more data, more JOINs, or a different set of indexes on the tables, it could be that a subquery produces a wildly different execution plan that is much slower than an equivalent JOIN.  And if you run the same query on a different version of SQL Server, the query optimizer may behave completely differently.

But what this did show me is that correllated subqueries are not ALWAYS more costly than a JOIN. So I don't necessarily have to avoid them if they are easier to use or make query design or prototyping faster or easier.

And now I know!


Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+



Category:
Link Owner:
Date Added:January 21, 2018 06:59:10 AM
Number Hits:0
RatingsAverage rating: (0 votes)
Reviews

No Reviews Yet.

 
GPWindow.com

Thank you for your support for GPWindow. It helps us cover part of the hosting costs for GPWindow.