SELECT查询加载耗时过长求助：执行时长超1分40秒

Hey there! Let's break down why your 400k-row query is taking 1-3 minutes to run, and walk through practical fixes to speed things up.

一、Why your query is dragging its feet #

Let's start with the core culprits:

• DISTINCT on a huge dataset: Returning 400k rows already means lots of data to process, and DISTINCT forces SQL to sort and compare every row to eliminate duplicates—this is a CPU and memory hog.
• Row-by-row function overhead: You're using Try_Parse, DatePart, DateAdd, and Substring on every single row in your select list. These are CPU-intensive operations that add up fast when dealing with hundreds of thousands of rows.
• Redundant subquery calculations: The subquery (Select Max(Aa.orderdate) From Return Aa Where Aa.Contractid = A.Contractid) runs once for every row in Return A where a contract ID matches. If you have thousands of unique contract IDs, that's thousands of repeated MAX() calculations.
• Missing indexes: The execution plan already flagged a missing index on the Action table—without this, SQL is probably doing full table scans to fetch data, which kills IO performance.
• Hash Join inefficiencies: Hash Joins work well for large datasets, but if your table stats are out of date, or you're using them on small tables, they can waste memory. If memory runs out, SQL spills the hash table to disk, making things even slower.

二、Actionable fixes to speed up your query #

Let's tackle these one by one, starting with the easiest wins.

1. First: Add the missing index the execution plan recommends #

This is a quick win that can immediately cut down on IO. Just replace the placeholder name with something descriptive and run it:

USE [SAmpler]
GO
CREATE NONCLUSTERED INDEX IX_Action_OrderID_IncludedColumns 
ON [dbo].[Action] ([orderID])
INCLUDE ([orderTypeID],[orderStatusID],[productID],[orderDate], [AssignedToUserID],[orderSubTypeID])
GO

This index covers all the columns your query needs from the Action table, so SQL won't have to do full table scans anymore.

2. Optimize the subquery to avoid repeated work #

Instead of running the MAX(orderdate) subquery for every row, precompute it once using a CTE (Common Table Expression):

WITH MaxOrderDatePerContract AS (
    SELECT Contractid, MAX(orderdate) AS MaxOrderDate
    FROM Return
    GROUP BY Contractid
)
Select Distinct 
    A.ID, tracactionId Contracid, Cancaldate, Suppliers, Ct.Type "Contract Type",
    Sitename "Site Name", St.Telephone "Site Telephone", Cc.Mobile, At.Type "Action Type",
    Ms.Status "Order Status", Name Client, Ass.Status,
    Isnull( 
        Case 
            When Try_Cast(orderac As Numeric(10,2)) <= 60000 Then 'T3' 
            When Try_Cast(orderac As Numeric(10,2)) Between 60001 And 1000000 Then 'T2' 
            When Try_Cast(orderac As Numeric(10,2)) >= 1000001 Then 'T1' 
        End, '-'
    ) "Consumption Order",
    Isnull( 
        Case 
            When Try_Cast(Replaceaq As Numeric(10,2)) <= 60000 Then 'T3' 
            When Try_Cast(Replaceaq As Numeric(10,2)) Between 60001 And 1000000 Then 'T2' 
            When Try_Cast(Replaceaq As Numeric(10,2)) >= 1000001 Then 'T1' 
        End, '-'
    ) "Consumption Replace",
    Case 
        When Datepart(Day, Cancaldate ) > 21 And Cancaldate < '9999-12-01' 
        Then Substring(Datename(Month, Dateadd(Month, 1, Cancaldate )), 1, 3) + ' ' + Datename(Year, Dateadd(Month, 1, Cancaldate )) 
        Else Substring(Datename(Month, Cancaldate ), 1, 3) + ' ' + Datename(Year, Cancaldate ) 
    End As "Month Year" 
From return A 
Left Hash Join Contract C 
    On C.Contractid = A.contractid 
    AND C.orderdate = (SELECT MaxOrderDate FROM MaxOrderDatePerContract WHERE Contractid = A.Contractid)
-- Keep all your existing joins here...
Left Hash Join Suppliers S On S.Suppliersid = C.Supplierid 
Left Hash Join ordercontract Mc On Mc.Contractid = C.Contractid 
Left Hash Join order M On M.orderid = Mc.orderid AND M.Meterstatusid <> 8 -- Moved filter here!
Left Hash Join Contracttype Ct On C.Contracttypeid= Ct.ordercontracttypeid 
Left Hash Join Site St On St.Siteid = C.Siteid 
Inner Hash Join ordersubtype Ast On Ast.ordersubtypeid = A.Aordersubtypeid 
Inner Hash Join ordertype At On At.ordertypeid = A.ordertypeid 
Left Hash Join oderstatus Ms On Ms.orderstatusid = M.orderstatusid 
Inner Hash Join Users U On U.Userid = A.userid 
Inner Hash Join orderstatus Ass On Ass.orderstatusid = A.orderstatusid 
Inner Hash Join product On A.productid = product.productid 
Inner Hash Join Contact Cc On product.Maincontactid = Cc.Contactid 
-- Removed the WHERE clause since we moved the filter to the M join

Notice I also swapped Try_Parse for Try_Cast—it's faster because it skips unnecessary cultural parsing logic.

3. Fix the M.Meterstatusid <> 8 filter #

Your original WHERE clause turns the Left Join to order M into an implicit Inner Join (since NULL <> 8 evaluates to unknown, those rows get filtered out). Moving this filter to the On clause of the join tells SQL to filter those rows early, before joining, which reduces the amount of data processed downstream.

4. Reassess if DISTINCT is really needed #

Take a close look at your join logic—do you actually get duplicate rows without DISTINCT? If the joins are properly constrained (e.g., using primary/foreign keys that enforce uniqueness), you might be able to drop DISTINCT entirely, which will save a ton of CPU and memory. If you do need it, try to narrow down the dataset first (filter early, join only necessary tables) before applying DISTINCT.

5. Precompute expensive calculations #

The Consumption Order, Consumption Replace, and Month Year columns require heavy per-row calculations. If this query runs often, consider:

• Adding computed columns to the relevant tables (e.g., a computed column for Consumption Order on the Return table)
• Precomputing these values in an ETL job or scheduled task and storing them in a table
• Using a materialized view to cache the results

6. Update statistics and check join types #

Out-of-date table statistics can make the query optimizer choose bad join strategies. Run UPDATE STATISTICS [TableName] for all tables involved in the query. Also, check the actual execution plan (hit Ctrl+M in SSMS before running the query) to see if there are any full table scans, key lookups, or expensive sort operations you missed.

三、Next steps to keep troubleshooting #

• Check server resources: Use SSMS's Activity Monitor to see if CPU, memory, or disk IO is maxed out. If CPU is high, focus on reducing function overhead; if disk IO is high, add more covering indexes.
• Test incrementally: Don't apply all fixes at once—test one change at a time to see which gives the biggest performance boost.

内容的提问来源于stack exchange，提问作者Skorpion