Sub query vs inline query...

• PiMané

  SSChampion

  Points: 13965

  More actions

  September 19, 2012 at 8:19 am

  #281914

  Hi,

  Can anyone tell me why SQL Server Execution Plan gives the same plan for these two queries?

  SELECT p.Product, p.Description, ISNULL(SUM(ps.Stock), 0) AS TotalStock FROM

  Products p LEFT JOIN ProductStorages ps ON p.Product = ps.Product

  SELECT p.Product, p.Description, (SELECT ISNULL(SUM(ps.Stock), 0) FROM ProductStorages ps WHERE p.Product = ps.Product) AS TotalStock FROM Products p

  The same execution plan, the same cost, the same scans, logical reads, physical reads, ....

  Does SQL Server "change" the queries to optimize them?!

  If I use WHERE p.Product = xxx the sub query is faster, 52% inline and 48% sub query, but still the same number of IO.

  Thanks,

  Pedro

  ---

  
  If you need to work better, try working less...

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  September 19, 2012 at 8:56 am

  #1539614

  I think that you didn't copy the entire code. Your first query should have a group by clause, which it hasn't.

  As for your question – the optimizer has several stages. One of the early stages produces a logical tree of steps that are needed to get the data. From that stage, the optimizer works with the tree and stop using the query text. Later on it tries to transform the tree by applying transformation rules and evaluate the original tree and modified trees. In short you may say, that it won't change the query itself, but it can change the logical tree that was originally created by from the query's text. If you are interested to know more about the optimization process, look for Benjamin Nevarez's free ebook and also read Paul Whites articles about it in SQLBlog.com.

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/