Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

New article series going on by Itzik Ben-Gan

I recommend you read it. Next part will contain a brilliant solution for calculating concurrent sessions in a linear algorithm. The math involved is very good indeed.

First part is found here http://www.sqlmag.com/articles/index.cfm?articleid=102734

//Peso

Print | posted on Monday, October 26, 2009 11:34 AM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Feedback

Gravatar

# re: New article series going on by Itzik Ben-Gan

You know that page is subscriber-only?
10/26/2009 7:35 PM | Arnold Fribble
Gravatar

# re: New article series going on by Itzik Ben-Gan

Oh, here's the gist of what I came up with last year:

SELECT MAX(s_hi) FROM (
SELECT d * (2*rd - ROW_NUMBER() OVER (ORDER BY t, d)) - (d+1)/2 AS s_hi
FROM (
SELECT 1 AS d, start AS t, ROW_NUMBER() OVER (ORDER BY start) AS rd
FROM dbo.Event

UNION ALL

SELECT -1 AS d, finish AS t, ROW_NUMBER() OVER (ORDER BY finish) AS rd
FROM dbo.Event
) AS A
) AS A

I was never quite sure what to count for a time when there were both events (sessions) starting and ending.
10/26/2009 7:44 PM | Arnold Fribble
Gravatar

# re: New article series going on by Itzik Ben-Gan

Arnold, that is really good! And adapted to multiple calculation used in Itzik's sample data,

SELECT app,1+MAX(s_hi) FROM (
SELECT app,d * (2 * rd - ROW_NUMBER() OVER (partition by app ORDER BY t, d)) - (d+1)/2 AS s_hi
FROM (
SELECT 1 AS d, app, starttime AS t, ROW_NUMBER() OVER (partition by app ORDER BY starttime) AS rd
FROM dbo.sessions

UNION ALL

SELECT -1 AS d, app, endtime AS t, ROW_NUMBER() OVER (partition by app ORDER BY endtime) AS rd
FROM dbo.sessions
) AS A
) AS A
group by app
10/26/2009 9:36 PM | Peso
Gravatar

# re: New article series going on by Itzik Ben-Gan

Well, as I said, the page is subscriber only, so I can only see the first two paragraphs of text, and I'm unlikely to pay $6 each month just to read the rest.
10/27/2009 10:07 AM | Arnold Fribble
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET