Solution 4: Cumulative Running Count
The fourth solution, which Listing 8 shows, uses an iterative approach involving a cursor and a loop. The code declares a cursor on a query that returns event types similar to the ones I used in the previous solution, sorted by application and event timestamp. The code forms a loop that iterates through all rows returned by the query I declared in the cursor. In each iteration, the code increments a counter called @concurrent when the current record contains a start event and decrements it when the current record contains an end event. I used 1 and -1 as event types, but you can choose other symbols—such as S and E—to help you distinguish between start and end events. You just have to examine the event type to determine whether to increment or decrement the counter instead of simply adding the event type to it.
A variable called @mx holds the maximum number of concurrent sessions the code has counted in the loop for the application so far. If @concurrent is greater than @mx, @mx receives the current value in @concurrent. When the application in the current record is different than the previous one, the code writes a row to a table variable called @AppsMx, which contains the previous application name (@prevapp) and maximum concurrent sessions (@mx).
About Performance
To test the performance of the different solutions, I populated the Sessions table with 16,000 rows by running the following code:
INSERT INTO Sessions(app, usr,
host, starttime, endtime)
SELECT app, usr, host,
starttime+n, endtime+n
FROM Sessions JOIN Nums
ON n <= 999
Note that to test the first solution, you also need to populate the Nums auxiliary table with enough rows for 1000 days (60*24*1000).
I tested various index combinations, but all the set-based solutions ran for such a long time, I eventually stopped them. The iterative solution ran for only 2 seconds on my laptop. The iterative solution is the only scalable solution that I came up with—the performance degradation of the cursor-based solution is linear as the table grows larger; with the set-based solutions, it's exponential. If you find a set-based solution that beats the iterative one, I'd love to hear about it.