大表关联含MAX与GROUP BY的SQL查询性能优化求助

Let's dive into why your query is taking 27 seconds and fix it—your row counts (1.3M+ visits, 5.2M+ movements) mean small index tweaks will make a massive difference.

First, Diagnose the Root Cause #

Your query fetches the latest VisitMovementID for each visit that ended in the last 4 hours. Looking at your existing indexes, two critical gaps stand out:

• VisitMovement Index Limitation: Your IDX_VisitMovement_VisitID only indexes VisitID, so when calculating MAX(VisitMovementID), SQL Server has to perform a Key Lookup (jump back to the clustered index) to grab the movement ID for every matching row. With 5M+ rows, this is a huge performance drain.
• Visit Index Redundancy: While IDX_Visit_EndDate filters on EndDate, it includes ClientID which your query doesn't use—though since VisitID is your clustered primary key, it's automatically included as a bookmark, so this isn't the main issue.

Step-by-Step Optimizations #

1. Create a Covering Index for VisitMovement (Biggest Performance Win)

This index will include everything the query needs, sorted so MAX() can be retrieved instantly without extra work:

CREATE NONCLUSTERED INDEX [IDX_VisitMovement_VisitID_MovementID] 
ON [dbo].[VisitMovement] ( 
    [VisitID] ASC, 
    [VisitMovementID] DESC -- Sort descending so the largest ID is the first row per VisitID
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

Why this works:

• SQL Server can quickly seek to all movements for a given VisitID
• The descending sort means the MAX(VisitMovementID) is the first row in each VisitID group—no sorting or lookup needed

2. Refresh Statistics to Ensure Optimal Query Plans

Outdated statistics can make SQL Server choose inefficient join strategies. Refresh them with full scans to get accurate data:

UPDATE STATISTICS [dbo].[Visit] WITH FULLSCAN;
UPDATE STATISTICS [dbo].[VisitMovement] WITH FULLSCAN;

3. Optional: Rewrite the Query for Better Readability (and Potential Plan Improvements)

While the index fix is critical, using CROSS APPLY can make the query logic clearer and sometimes lead to more efficient execution plans:

DECLARE @RecentlyLeftDate datetimeoffset(7);
SELECT @RecentlyLeftDate = DATEADD(hh, -4, SYSDATETIMEOFFSET());

SELECT VM.MaxVisitMovementID
FROM Visit V
CROSS APPLY (
    SELECT MAX(VisitMovementID) AS MaxVisitMovementID
    FROM VisitMovement VM
    WHERE VM.VisitID = V.VisitID
) VM
WHERE V.EndDate > @RecentlyLeftDate;

What to Verify in Your Execution Plan After Changes #

• Check for Key Lookup operators on VisitMovement—these should disappear entirely with the new index.
• Look for expensive Sort or Hash Match operators—they should be replaced with efficient Index Seek or Nested Loops operations.

If you still see slow performance after implementing these changes, feel free to share your updated execution plan or additional table schema details, and we can dig deeper.

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