October 17, 2007 at 9:59 pm
We have an application where we need to determine how many "Sessions" are active at any given second. Each session is logged to a Sessions table that has fields ID, StartTime, and EndTime.
For example, say Session1 starts at 10/17/2007 10:45:00 PM and ends at 10/17/2007 10:45:35 PM, and Session2 starts at 10/17/2007 10:45:15 PM and ends at 10/17/2007 10:45:30 PM. We need to be able to show there were 2 sessions in use between 10:45:15 PM and 10/17/2007 10:45:30 PM.
I have tried making a SecondsDimension table that stores each second of every day, and for a given interval will calculate how many Sessions span each second within the interval, but as you can imagine, the SecondsDimension table is huge and that approach takes forever. It ran at least 3 minutes before I killed it so there is no telling how long that would take...
I also tried looping through the every second for a specified interval in C# code, and then calculating how many Sessions span the current second, but the loop alone without touching the DB also takes several minutes...
Any ideas??
October 17, 2007 at 10:20 pm
Not sure if i am right in understanding what you are trying to achieve, but
I've created a test table
CREATE TABLE [dbo].[tmpTimeSpans](
[UserID] [int] NULL,
[Start] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL
) ON [PRIMARY]
and then added following values
INSERT tmpTimeSpans([UserID],[Start],[EndTime])
VALUES (1,'2007-10-18 04:01:10','2007-10-18 04:01:50')
INSERT tmpTimeSpans([UserID],[Start],[EndTime])
VALUES (2,'2007-10-18 04:01:20','2007-10-18 04:02:50')
now if i run the following queries
DECLARE @pointInTime datetime
SELECT @pointInTime='2007-10-18 04:02:30'
SELECT *
FROM tmpTimeSpans
WHERE @pointInTime BETWEEN [Start] AND [EndTime]
with @pointInTime being
'2007-10-18 04:01:15' or '2007-10-18 04:01:30' or '2007-10-18 04:02:30' i will get user 1 only, user 1&2 and user 2 only respectively
October 17, 2007 at 11:54 pm
That is pretty much what I want to do. but in your example, the @pointInTime will be every second for a given interval. For example, on the web-front end the user supplies a StartDate of '10/1/2007' and an EndDate of '10/15/2007'. The report must then calculate how many simultaneous sessions there are for every second (@pointInTime) between StartDate and EndDate. The query you supplied does exactly that, but that must be done for EVERY second of the user supplied interval. Ultimately, the report will show a line graph where the X axis is time and the Y axis are the number of concurrent sessions.
So, you're on the right track, but there is still some more to it. I believe I need a query similar to the statement below, but it has been running for about an hour, searching only a small window of time, and still not complete... I'd hate to try to run the query for a larger interval, such as a day or week!
select sd.FullDateTime, count(s.ID) TotalSessions
from secondsdimension sd left outer join Sessions s
on sd.FullDateTime between s.StartTime and s.EndTime
where sd.FullDateTime between '10/15/2007 12:00' and '10/15/2007 13:59:59'
group by sd.FullDateTime
FYI - secondsdimension is a table that contains every second of every day, so it is huge and likely the reason this thing takes so long...
October 18, 2007 at 12:14 am
how i understand this:
i have a logging in place which writes to the table when user starts and in the same row marks the end time when user finishes his work. For this i only need one table.
So, when someone requests a chart of user activity for the last one hour i would then run the first query 3600 times incrementing the @pointOfTime by one second.
So we only need one table which will be smaller comparing to the second table you had to create.
October 18, 2007 at 12:48 am
Yes, that is the idea. I think this is similar to your idea:
declare @dtCurrent datetime
declare @iTotal int
select @dtCurrent='10/15/2007 12:00'
while (@dtCurrent < '10/15/2007 13:00')
begin
select @iTotal=count(ID)
from Sessions
where @dtCurrent between StartTime and EndTime
print 'DateTime: ' + convert(varchar, @dtCurrent) + '; Total: ' + convert(varchar, @iTotal)
select @dtCurrent = dateadd(ss, 1, @dtCurrent)
end
This has been running for about 16 minutes and is only into the 5th minute of the hour! Looks like a long way to go. Neither of the solutions discussed are satisfactory, so I'm wondering if it might be a good idea to try to "batch" this operation so when the user runs the report, the report hits a table that has pre-calculated the number of concurrent sessions. It seems that table would be a huge table though - again with an entry for every second of every day, plus the total number of active sessions during that slice of time. And still there would be the problem of how often to run the batch, especially if it takes a while to run...
October 18, 2007 at 12:57 am
have you tried analyzing your query in the Database Engine Tuning Advisor? How big is the Sessions tabe? What is the spec of the server?
October 18, 2007 at 1:45 am
The table has about 5.5 million rows, and if it helps, the properties of the table specify 555MB of data space and 200MB of index space. I don't know the specs of the server but it should be sufficient as it is an enterprise grade system with clustering, fiber storage, etc... Also, I'm not familiar with a query tuning advisor, so the answer to that question is no!
Now I'm considering changing the logging so that when the EndTime is logged, I will determine the timespan for the Session and increment counters for every second in the timespan. Still this goes back to having a huge table with entries for every second of every day, but I think it will at least make summarizing the data fast and easy. Then maybe the only thing to worry about is generating the report from the huge table?
October 18, 2007 at 2:15 am
Yes, I think that's the way to go. Either put a trigger on your table that updates another table, SessionCount, say, whenever a session starts or finishes, or write a query that uses the start and end times from your session table to increment and decrement your session count. You don't need to do it for every second of the day: your graphing tool should be able to interpolate by drawing a line between one point and the next.
John
October 18, 2007 at 2:22 am
Are you trying to collapse datetime ranges?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88422
N 56°04'39.16"
E 12°55'05.25"
October 18, 2007 at 2:57 am
Rowdy Johnson (10/18/2007)Still this goes back to having a huge table with entries for every second of every day
Who told you it's gonna be a huge table?
There are 86400 seconds per day, it gives you about 31.5M rows for a whole year.
If to consider that this table will have 2 int columns - "seconds counter" and "sessions opened", then it comes to total 252M of data for entire year.
With clustered index on "seconds counter" performance will be brilliant for both selects and updates.
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply