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)