0

Recently I want to improve some of my queries and I see something that I don't really understand, here is my query :

SELECT   
    S.VENTE_GUID, Top6QtySold.list
FROM 
    SALES s with(nolock) 
OUTER APPLY 
    (SELECT 
         STUFF((SELECT TOP(6) ',' + t.CodeProd
                FROM topSold t 
                WHERE t.VENTE_GUID = s.SALE_GUID
                ORDER BY t.nbOrdered DESC
                FOR XML PATH('')), 1, 1, '') AS list
     ) AS Top6QtySold

When I execute this query, it takes around 6 seconds, but when I comment out the column: op6QtySold.list in the SELECT statement, it takes less than 1 second.

I know that the number of columns in the SELECT statement can decrease performance but in this case, the hard work has been done by the OUTER APPLY and it should be a formality to display the value of Top6QtySold, no ?

(I simplify the query but imagine that there is a lot of data in the SELECT part and more joins)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ElRoro
  • 203
  • 1
  • 13
  • provide a sample of data to get better recommendation from community – RF1991 Mar 17 '22 at 09:27
  • Compare the execution plans of the queries. – Serg Mar 17 '22 at 09:36
  • When I compare the executions plans, I see that when I comment on the `op6QtySold.list`, it's like SQL don't care about the `OUTER APPLY` and do nothing except search in the `SALES` table. – ElRoro Mar 17 '22 at 09:45
  • 1
    Exactlty, its nothing to do with STUFF, the thing slowing it down is the correlated subquery with TOP/ORDER that joins to a row in the main query. When you comment out Top6QtySold.list the optimizer realises it can ditch the subquery – codeulike Mar 17 '22 at 09:47
  • Or in other words: Your 'OUTER APPLY' is an example of https://en.wikipedia.org/wiki/Correlated_subquery and they are generaly slow, especially with things like TOP and XMLPATH. You could try sticking a few indexes here or there. Ultimately for things like this I would have a temporary table that gets prepared in advance and then join to that. – codeulike Mar 17 '22 at 09:49
  • Ok, I thought it doesn't matter which columns in the SELECT, joins and OUTER APPLY were always "executed" – ElRoro Mar 17 '22 at 09:49
  • Replace the `for xml` trick with `string_agg` (requires SQL Server 2017 or later). – Salman A Mar 17 '22 at 10:49
  • Stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) – SMor Mar 17 '22 at 11:32
  • @SMor maybe you can argue a little bit... In my case, I should use it – ElRoro Mar 17 '22 at 11:58
  • @SalmanA Yes but I'm on compatibility level 100, going to update next week, I keep your advice – ElRoro Mar 17 '22 at 12:38

0 Answers0