APPLY vs. Correlated Scalar Subquery in SELECT clause

• _ms65g_

  SSCommitted

  Points: 1504

  More actions

  April 28, 2010 at 6:32 am

  #232121

  Hi,

  Is better we use APPLY instead of correlated scalar subqueries in SELECT clause in any case on not?

  For example these two queries will show us the same result:

  First Query (Subquery)

  SELECT t.*, list = (SELECT OrderID + ', '

  FROM Orders

  WHERE customerID = t.customerID

  FOR XML PATH(''))

  FROM Customers T

  Second Query (APPLY)

  SELECT t.*, D.list

  FROM Customers T

  OUTER APPLY (SELECT orderID + ', '

  FROM Orders

  WHERE customerID = T.customerID

  FOR XML PATH('')) D(list)

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  April 28, 2010 at 7:15 am

  #1158884

  Personally, I'd say yes, use the APPLY operation, but, as with all things, you should test both, look at the execution plans, the performance time, and then make the determination that way.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Jeff Moden

  SSC Guru

  Points: 1004372

  More actions

  April 28, 2010 at 8:39 am

  #1158991

  I'd say "It Depends". In theory, they should have about the same execution plan. In reality, you need to turn on SQL Profiler and check out things like Duration vs CPU time used, reads, writes, and row counts.

  Then, you need to build a really large test table (I typically gen a million rows to check for scalability) and run it again.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  April 28, 2010 at 9:25 am

  #1159052

  In terms of performance on queries like your example, I agree there should be very little difference, although as Jeff said, that may not be true for very large datasets.

  However, there is one example where you just cannot use a subquery, but can use APPLY.

  I have not thought of a good reason to do this, but it shows that there is a difference between them.

  You cannot use an aggregate on a subquery, whereas you can on an APPLY.

  For example

  -- This is not allowed

  SELECT g.GroupName ,COUNT( (SELECT a.id FROM newsdata.dbo.Articles AS a WHERE a.GroupName = g.GroupName ) ) AS id_count

  FROM newsdata.dbo.Groups AS g

  GROUP BY g.GroupName

  -- you get this error

  -- Msg 130, Level 15, State 1, Line 3

  -- Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

  -- this IS allowed

  SELECT g.GroupName ,COUNT( ax.id ) AS id_count

  FROM newsdata.dbo.Groups AS g

  OUTER APPLY (SELECT a.id FROM newsdata.dbo.Articles AS a WHERE a.GroupName = g.GroupName ) AS ax(id)

  GROUP BY g.GroupName

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]