Calculate concurrent user sessions on Access DB

Hi, I have an Access 2016 database which has a table with user sessions (session ID's, application name, session start date/time and session end date/time). I need to find out the maximum concurrency for each application for each date.

I currently have a query as below which is taking too long (20 mins) to run but also comes out with only one day. Please can someone help with the algorithm for concurrency calculation  for Access?

SELECT TOP 1 Count(*) AS ['concurrency'], T1.Date
FROM table AS T1, table AS T2
WHERE (((T1.startTime_f) Between [T2].[startTime_f] And [T2].[endTIme_f]))
GROUP BY T1.usersessionID, T1.Date, t2.date;
kalicsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bfuchsCommented:
Would suggest an index on the startTime_f field.
If that doesn't help, perhaps you can move over this table to SQL Server Express (free edition).
Then Access is capable of executing Pass-Through queries really fast.
0
Gustav BrockCIOCommented:
First, create a query that lists the timeslots used:

SELECT 
    UserLog.StartTime, 
    UserLog.EndTime
FROM 
    UserLog
GROUP BY 
    UserLog.StartTime, 
    UserLog.EndTime;

Open in new window

Save this as TimeSlots.

Then find the count of users having used any of these time slots:

SELECT 
    TimeSlots.StartTime, 
    Count(*) AS Users
FROM 
    UserLog, 
    TimeSlots
WHERE 
    (TimeSlots.StartTime Between [UserLog]![StartTime] And [UserLog]![EndTime]) 
    OR 
    (TimeSlots.EndTime Between [UserLog]![StartTime] And [UserLog]![EndTime])
GROUP BY 
    TimeSlots.StartTime;

Open in new window

Save this as TimeSlotUsers.

Finally, find the maximum count of concurrent users:

SELECT 
    DateValue([StartTime]) AS [Date], 
    Max(TimeSlotUsers.Users) AS MaxConcurrentUsers
FROM 
    TimeSlotUsers
GROUP BY DateValue([StartTime]);

Open in new window

Of course, indexes on the time fields is a must.
0
kalicsAuthor Commented:
Thanks very much Gustav. This works perfectly. Only one little question though - I have user session data for multiple applications on the same table. These queries work perfectly for concurrency for one application.

When I did try to customise this for multiple applications, it comes up with the output of different applications listed, but it doesn't consider each application as unique & does not calculate concurrency for that application, but considers only the time fields and therefore provides the concurrency for the overall database.

Can you please help with any thoughts as to how to list max concurrency for each application?

Much appreciated
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Gustav BrockCIOCommented:
That could be (air code):

SELECT 
    ApplicationId,
    UserLog.StartTime, 
    UserLog.EndTime
FROM 
    UserLog
GROUP BY 
    ApplicationId,
    UserLog.StartTime, 
    UserLog.EndTime;


SELECT 
    ApplicationId,
    TimeSlots.StartTime, 
    Count(*) AS Users
FROM 
    UserLog, 
    TimeSlots
WHERE 
    (UserLog.ApplicationId = TimeSlots.ApplicationId)
    AND
    ((TimeSlots.StartTime Between [UserLog]![StartTime] And [UserLog]![EndTime]) 
    OR 
    (TimeSlots.EndTime Between [UserLog]![StartTime] And [UserLog]![EndTime]))
GROUP BY 
    ApplicationId,
    TimeSlots.StartTime;


SELECT 
    ApplicationId,
    DateValue([StartTime]) AS [Date], 
    Max(TimeSlotUsers.Users) AS MaxConcurrentUsers
FROM 
    TimeSlotUsers
GROUP BY 
    ApplicationId,
    DateValue([StartTime]);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kalicsAuthor Commented:
Thanks very much Gustav. It worked like magic. Can't thank you enough.
0
kalicsAuthor Commented:
Thank you so much. Incredibly helpful.
0
Gustav BrockCIOCommented:
You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.