SQL Server按应用和国家统计分钟级并发会话数的查询优化

Hey there! That nested loop approach you're using is definitely going to drag performance down—especially as your TestSession table grows. Let's swap that out for a set-based solution, which is how SQL Server is designed to work efficiently.

Why Your Current Query Is Slow #

Your nested loops run thousands of individual SELECT and INSERT operations. Each loop iteration hits the TestSession table separately, which adds massive overhead from repeated I/O and query parsing. Set-based queries let the query optimizer batch the work into a single, optimized operation.

Efficient Set-Based Solution #

This query will generate all required (minute, application, country) combinations, count concurrent sessions for each, and insert the results in one go:

-- CTE to create all valid dimension combinations: minute, application, country
WITH AllDimensions AS (
    SELECT
        dt.DateTimeKey,
        dt.DateTime AS MinuteTime,
        a.Application_ID,
        c.Id_Country
    FROM
        dbo.DimDateTime dt
    -- Cross join to get every combination of minute, app, and country
    CROSS JOIN dbo.Application a
    CROSS JOIN dbo.Country c
    -- Filter to your target time range (adjust as needed)
    WHERE
        dt.DateTime BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 00:01:00'
),
-- Calculate concurrent sessions for each dimension combination
ConcurrentCounts AS (
    SELECT
        ad.DateTimeKey,
        ad.Application_ID,
        ad.Id_Country,
        -- Count sessions that were active during this minute
        COUNT(ts.SessionID) AS Nbr_Connection
    FROM
        AllDimensions ad
    -- Left join ensures we get 0 for combinations with no active sessions
    LEFT JOIN dbo.TestSession ts
        ON ts.Application_ID = ad.Application_ID
        AND ts.Id_Country = ad.Id_Country
        AND ts.StartDate < ad.MinuteTime
        AND ts.EndDate > ad.MinuteTime
    GROUP BY
        ad.DateTimeKey, ad.Application_ID, ad.Id_Country
)
-- Insert results into your TotalConnections table
INSERT INTO dbo.TotalConnections (DateTimeKey, Application, IsoCounty, Nbr_Connection)
SELECT
    DateTimeKey,
    Application_ID AS Application,
    Id_Country AS IsoCounty,
    Nbr_Connection
FROM ConcurrentCounts;

Extra Optimizations #

1. Filter Unneeded Combinations: If you only care about (app, country) pairs that actually have sessions, replace the CROSS JOIN dbo.Application a and CROSS JOIN dbo.Country c with a distinct list from TestSession:

   CROSS JOIN (SELECT DISTINCT Application_ID, Id_Country FROM dbo.TestSession) AS ActivePairs
   

   This reduces the number of combinations the query needs to process.

2. Add a Composite Index: For large TestSession tables, create this index to speed up the join and count operations:

   CREATE NONCLUSTERED INDEX IX_TestSession_AppCountry_Dates
   ON dbo.TestSession (Application_ID, Id_Country, StartDate, EndDate)
   INCLUDE (SessionID);
   

   This index lets SQL Server quickly find all sessions matching a given app, country, and time range without scanning the entire table.

How This Matches Your Original Logic #

• It includes every minute in your target time range (just like your loop increments @HeureCurrent)
• It covers every application and country (matching your nested loops over @id_application and @id_pays)
• It counts sessions where StartDate < minute and EndDate > minute (same as your inner SELECT)
• It inserts 0 for combinations with no active sessions (thanks to the LEFT JOIN)

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