按分钟统计时间范围内在线用户数的SQL查询性能优化求助

Hey there! Let's fix that slow query and get your active user count per minute running smoothly.

The Problem with Your Original Query #

Your initial approach uses a correlated subquery, which means for every row in entrytable, the database runs an entirely separate count query. This creates an O(n²) time complexity—when your table grows even a little, it'll grind to a halt (or worse, timeout). We need a better way to avoid all that redundant work.

Optimized Solution #

The key is to first generate a continuous sequence of the exact minutes we need to count, then join that sequence with your user session data to calculate active users in one pass. Here's how to do it in SQL Server (since you're using DATEADD/DATEDIFF):

Step 1: Generate a Continuous Time Series

First, we'll create a list of every whole minute between your earliest session start and latest session end:

-- Grab the time range we need to cover (rounded to whole minutes)
DECLARE @StartMinute DATETIME, @EndMinute DATETIME;
SELECT 
    @StartMinute = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, MIN(start_time)), 0),
    @EndMinute = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, MAX(end_time)), 0)
FROM entrytable;

-- Recursive CTE to build our minute-by-minute sequence
WITH TimeSeries AS (
    SELECT @StartMinute AS MinuteTime
    UNION ALL
    SELECT DATEADD(MINUTE, 1, MinuteTime)
    FROM TimeSeries
    WHERE MinuteTime < @EndMinute
)

Step 2: Count Active Users for Each Minute

Now join this time series with your session data to count users who were online during each minute:

SELECT 
    ts.MinuteTime AS [time],
    COUNT(DISTINCT et.user_name) AS [active no of users]
FROM TimeSeries ts
LEFT JOIN entrytable et 
    ON et.start_time <= ts.MinuteTime 
    AND et.end_time > ts.MinuteTime -- User was active during this minute
GROUP BY ts.MinuteTime
ORDER BY ts.MinuteTime
OPTION (MAXRECURSION 0); -- Add this if your time range exceeds 100 minutes

Why This Works Better #

• Single Pass Logic: Instead of running hundreds/thousands of subqueries, we do one join and one group by. This drops the complexity to O(n + m), where m is the number of minutes we're counting (way smaller than n for most datasets).
• Clearer Logic: It's easier to read and maintain—you can see exactly which minutes we're checking and how we're matching sessions.

Bonus: Speed It Up Even More #

If your entrytable is large, add this index to let the database quickly find relevant sessions without scanning the entire table:

CREATE INDEX IX_entrytable_start_end_user ON entrytable (start_time, end_time, user_name);

This index covers all the columns we need for the join and count, so the database doesn't have to "jump back" to the main table data.

Expected Output #

Running this query will give you exactly the result you want:

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