SQL Server: query performance

Question

SQL Server: query performance

JDias 161

I would like to write a fast query returning the data that the following query returns:

SELECT M,COmb,Mod,Loc,Initial,
Final,Kms_,R,Franq,Num_Fleet,Pneus,Cli,Active,Interv,
Cost1,Cost2,Name,Colab_Cod,col.colName
FROM tFleetCars AS v
OUTER APPLY (
    SELECT TOP(1) Name FROM vFleetExpenses WHERE M=v.M ORDER BY Yr DESC, Mth DESC
) AS d (Name)
LEFT JOIN DB2.dbo.tCol AS col ON col.colID=Colab_Cod

This query returns about 400 lines but takes minutes to complete. If I remove the inner query (SELECT TOP(1) ...) the query only takes a few seconds.

How to make this query efficient? I would like to have a WHERE clause with a MAX, but this is imposible...

I am hoping that this query will take only a few seconds. I think the server where this query runs is very slow...

Note: Yr is year and Mth is month...

EchoLiu-MSFT 14,621 Reputation points

2021-11-16T10:01:56.867+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Answer accepted by question author

2 additional answers

Your answer

EchoLiu-MSFT 14,621 Reputation points

2021-11-16T10:01:56.867+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Answer 1

Guoxiong 8,211

Try this:

;WITH CTE_vFleetExpenses AS (
    SELECT t.[Name], t.M
    FROM (
        SELECT [Name], M, ROW_NUMBER() OVER(PARTITION BY M ORDER BY Yr DESC, Mth DESC) AS RN
        FROM vFleetExpenses
    ) AS t
    WHERE t.RN = 1
)

SELECT v.M,COmb,Mod,Loc,Initial,
 Final,Kms_,R,Franq,Num_Fleet,Pneus,Cli,Active,Interv,
 Cost1,Cost2,d.Name,Colab_Cod,col.colName
 FROM tFleetCars AS v
INNER JOIN CTE_vFleetExpenses AS d ON d.M = v.M
 LEFT JOIN DB2.dbo.tCol AS col ON col.colID=Colab_Cod

JDias 161 Reputation points

2021-12-28T10:29:14.377+00:00

I had to change INNER JOIN CTE_vFleetExpenses to LEFT JOIN to have the same result.

The final query lasts only 1 second!

Answer 2

Erland Sommarskog 127.4K MVP Volunteer Moderator

If you are on a version recent enough, you can try to add this at the end:

OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))

Naomi Nosonovsky 8,771 Reputation points

2025-09-03T15:33:40.0333333+00:00
I'm on Microsoft SQL Server 2019 (RTM-CU32-GDR) (KB5058722) - 15.0.4435.7 (X64)

Jun 9 2025 18:36:12 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor) compatibility level: 150 and this hint is not available for me.

OPTION (FAST 10) worked and gave a slight improvement in speed.

UPDATE. Found the name of the hint: https://blog.sqlauthority.com/2020/12/28/sql-server-disable-rowgoal-optimizer/ It did help for my query as well.
Erland Sommarskog 127.4K Reputation points MVP Volunteer Moderator

2025-09-03T20:50:29.1866667+00:00

Sorry, there was an underscore too many. I have corrected the post. SQL 2019 has the hint.

Answer 3

Hi @JDias ,

Sometimes using OUTER APPLY will perform a clustered index scan, in order to optimize performance, consider using LEFT JOIN instead of OUTER APPLY:

    ;WITH cte  
    as(SELECT Name,ROW_NUMBER() OVER(PARTITION BY M ORDER BY Yr DESC, Mth DESC) rr  
    FROM vFleetExpenses )  
      
    SELECT v.M,COmb,Mod,Loc,Initial,  
     Final,Kms_,R,Franq,Num_Fleet,Pneus,Cli,Active,Interv,  
     Cost1,Cost2,Name,Colab_Cod,col.colName  
    FROM tFleetCars AS v  
    LEFT JOIN cte c ON v.M=c.M AND c.rr=1  
    LEFT JOIN DB2.dbo.tCol AS col ON col.colID=Colab_Cod

Since there is no relevant sample data to test, the above code is untested code. If this does not solve your problem, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) ,So that we’ll get a right direction and make some test.

Regards,
Echo

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Erland Sommarskog 127.4K Reputation points MVP Volunteer Moderator

2021-11-15T22:38:39.487+00:00

Sometimes using OUTER APPLY will perform a clustered index scan,

To be precise, the OUTER APPLY as such does not have to result in a CI scan. What is likely to the culprit is the TOP(1). This establishes a so called row goal. The optimizer settles for the scan, which will stop as soon as there is a hit, and the optimizer optimistically think that this will happens soon. When the optimizer is mistaken, the result is a disaster. (And when the optimizer ir right, the gain is modest.)

This is why I suggested the OPTION hint in my post.

Share via

SQL Server: query performance

2 additional answers

Your answer