Why selecting STUFF value decrease performance of a query in SQL Server

465 views Asked by At

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)

0

There are 0 answers