SQL Server 2012数据库性能缓慢求助：联合主键视图查询优化

Hey there, let's work through this performance slowdown issue with your view that fetches the latest record per user. Based on your setup (750k total records, UserID+Date as composite unique key, no deletes, max 50 records per user), here are actionable optimizations you can implement:

1. 优化核心索引（最关键的一步） #

Your current composite unique key is UserID + Date, but to speed up fetching the latest record per user, you need a covering index that eliminates expensive key lookups or table scans. Create an index ordered by UserID first, then Date in descending order, and include all columns your view needs to return:

CREATE NONCLUSTERED INDEX IX_Users_UserID_DateDesc
ON users (UserID, Date DESC)
INCLUDE (Username /* add any other columns your view selects here */);

This index lets SQL Server directly retrieve the latest row for each UserID without hitting the base table, since all required data is stored in the index itself.

2. 重构视图查询逻辑 #

If your current view uses ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY Date DESC) to filter for rn = 1, try switching to a CROSS APPLY approach—it often performs better with the right index:

CREATE VIEW vw_LatestUserRecords
AS
SELECT u.Username, u.UserID
FROM (SELECT DISTINCT UserID FROM users) AS distinctUsers
CROSS APPLY (
    SELECT TOP 1 Username, UserID
    FROM users
    WHERE UserID = distinctUsers.UserID
    ORDER BY Date DESC
) AS u;

Alternatively, if you prefer a more concise syntax, TOP 1 WITH TIES can work too:

CREATE VIEW vw_LatestUserRecords
AS
SELECT TOP 1 WITH TIES Username, UserID
FROM users
ORDER BY ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY Date DESC);

Test both to see which plays nicer with your index and data distribution.

3. 考虑使用索引视图（持久化视图） #

If this view is queried frequently and you can tolerate minor latency in data freshness (since you don't delete records, inserts are the only updates), create an indexed view. This stores the view's results physically, so queries hit pre-computed data instead of calculating on the fly:

-- First, create the view with SCHEMABINDING
CREATE VIEW vw_LatestUserRecords_Indexed
WITH SCHEMABINDING
AS
SELECT UserID, Username, Date,
       ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY Date DESC) AS rn
FROM dbo.users; -- Must use two-part name for SCHEMABINDING

-- Then create a unique clustered index on the view
CREATE UNIQUE CLUSTERED INDEX IX_vw_LatestUserRecords_Indexed
ON vw_LatestUserRecords_Indexed (UserID, rn);

Note: Indexed views have restrictions (e.g., no DISTINCT in some cases, must use SCHEMABINDING), but they're powerful for read-heavy workloads.

4. 更新统计信息 #

Outdated statistics can lead SQL Server's query optimizer to choose bad execution plans. Refresh statistics for the users table to ensure the optimizer has accurate data distribution info:

UPDATE STATISTICS dbo.users WITH FULLSCAN;

Run this periodically, especially after large batches of inserts.

5. 分析执行计划 #

Always check the actual execution plan when troubleshooting performance:

• Look for Table Scans or Clustered Index Scans: These mean SQL Server is reading the entire table—your covering index should eliminate this.
• Look for Key Lookups or RID Lookups: These indicate your index is missing columns needed for the query, so add them to the INCLUDE clause of your index.
• Check for Sort operators: If sorting is happening, your index's order (UserID + Date DESC) should handle this, so make sure the index is being used.

6. 分区表（可选，针对未来增长） #

While 750k records isn't massive, if your data will grow significantly, consider partitioning the users table by Date. This can speed up queries that filter on date ranges, though for your specific view (fetching latest per user), the covering index is likely sufficient.

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