Adding sort destroys simple SQL query-CodePudding

I have a fairly simple query that is supposed to return only a single number – the ID of the TOP record from the query. It is not especially elegant, but it works well, except for the Sort clause. When I select and execute all but the Sort clause, it executes instantly, but naturally does not return the proper first record. When I add the sort, it hangs. I have let it run around ten minutes while watching the task manager. It shows regular peaks of 100% CPU usage alternately on two of the server's four cores, but does not bog the machine in any noticeable fashion.

When I run just the inner part, it returns 6070 records. Sorting that and returning the first one should be a completely trivial task, but that is not the case. I can't even check the execution plan, because it never finishes, at least not in the amount of time I am willing to let it run.

What can be going wrong, that SQL Server cannot properly handle such a simple query?

declare @Taxonomie nvarchar(max) = '%rupicapra%'

SELECT Top 1 AkcesAutoID, PP.Rok, PP.AkcesitPred, PP.Akcesit
FROM (
    SELECT    
        P.AkcesAutoID, A.AkcesitPred, A.Akcesit, A.Rok    
    FROM dbo.Podrobnosti P    
    LEFT JOIN dbo.vwFirstSynonymika S ON P.PodrobnostiAutoID = S.PodrobnostiAutoID    
    INNER JOIN dbo.Akces A ON P.AkcesAutoID = A.AkcesAutoID    
    LEFT JOIN dbo.Taxonomy T ON P.TaxonAutoID = T.TaxonAutoID    
    WHERE (T.CompleteTaxonText Like @Taxonomie)    
) PP    
ORDER BY PP.Rok, PP.AkcesitPred, PP.Akcesit

CodePudding user response：

Does this get a better plan than the existing subquery? Post both to paste the plan and update the question with the table/index structure. This will more likely be fixed by adjusting indexes than changing the query.

declare @Taxonomie nvarchar(max) = N'%rupicapra%';
------ also important! ------------^

--  SELECT Top 1 AkcesAutoID, PP.Rok, PP.AkcesitPred, PP.Akcesit
--  FROM 
--  (
    SELECT P.AkcesAutoID, A.AkcesitPred, A.Akcesit, A.Rok    
      FROM dbo.Podrobnosti P    
      INNER JOIN dbo.Akces A 
        ON P.AkcesAutoID = A.AkcesAutoID
      WHERE EXISTS 
      (
        SELECT 1 FROM dbo.Taxonomy T 
          WHERE P.TaxonAutoID = T.TaxonAutoID 
          AND T.CompleteTaxonText Like @Taxonomie
      )    
--  ) PP    
--  ORDER BY PP.Rok, PP.AkcesitPred, PP.Akcesit

CodePudding user response：

Why an ORDER BY kills my query?

When you run the query without the ORDER BY, SQL Server only needs to find the first row that meets the JOIN and WHERE clauses. It does not care which row meets that condition, just that at least one row does, and it returns it. It stops scanning any additional rows at that point, and returns the single row.

However, when you add the ORDER BY, SQL Server must now read ALL data returned by your query into memory, not just the first row (or pages) that match. Then, it must sort all of that data before it knows which row will be the TOP 1.

This could be the difference or reading a few MB of data into memory, or many 100s of GB of data into memory. All depending on the size of your tables.

That is a high level reason of why ORDER BY often kills a query plan.

Adding your plans for both queries and any indexes that exists on the tables would be helpful to evaluate further. You can paste your plans at pastetheplan.com.