求助：优化SQL Server中Last Year Attended（LYA）查询性能

Hey Mike, let's dig into optimizing that LYA query that's been taking over 20 minutes—totally understand how frustrating that slow runtime can be!

First, let's recap the core logic to make sure we're aligned: For each user and specific activity, we need to find the second most recent attendance year (the year right before their latest participation). If they only attended once, LYA should return NULL.

The Most Efficient Approach: Window Functions #

Window functions like LAG() are built exactly for this kind of "get the previous value" scenario, and they’re way more efficient than nested subqueries or self-joins that scan the table multiple times. Here’s a sample implementation assuming your table is named user_activity with columns user_id, activity_id, and attend_year:

WITH user_activity_ranked AS (
    SELECT
        user_id,
        activity_id,
        attend_year,
        -- Rank years from newest to oldest per user+activity
        RANK() OVER (PARTITION BY user_id, activity_id ORDER BY attend_year DESC) AS year_rank,
        -- Grab the year immediately before the most recent one
        LAG(attend_year) OVER (PARTITION BY user_id, activity_id ORDER BY attend_year DESC) AS prev_attended_year
    FROM user_activity
    -- Add filters here if you only need specific activities/users to reduce data upfront!
    -- WHERE activity_id = 'your_target_activity'
)
SELECT
    user_id,
    activity_id,
    -- Only return the previous year for the latest attendance record
    CASE WHEN year_rank = 1 THEN prev_attended_year ELSE NULL END AS last_year_attended
FROM user_activity_ranked
WHERE year_rank = 1; -- Focus only on the most recent attendance entry per user+activity

Key Optimizations to Speed This Up #

• Add a Composite Index: Create an index on (user_id, activity_id, attend_year DESC). This lets the database handle the partitioning and sorting in the window function without doing a full table scan or expensive in-memory sorts.
• Filter Early: If you’re only targeting specific activities, add that WHERE clause inside the CTE to reduce the number of rows the query processes right from the start.
• Check Execution Plans: Pull up your database’s execution plan (e.g., EXPLAIN ANALYZE in PostgreSQL) to spot bottlenecks—look for full table scans, sort operations with high cost, or repeated table access.

Since you mentioned you found a more efficient version than the original, compare it against this approach! Chances are the better version is leveraging window functions or reducing redundant table scans. If you’re still seeing slowdowns, check if your table is partitioned (e.g., by year) to further narrow down the data the query needs to touch.

Hope this helps get that runtime down from 20+ minutes to something manageable!

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