SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Maximum Concurrent Users in a day


Maximum Concurrent Users in a day

Author
Message
Kwisatz78
Kwisatz78
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1085 Visits: 1821
Hi all

I have a table which contains login and logout times for a large set of users, and we are wanting to know how to code it to pull back the maximum number of users who are logged on at any one time during that day.

I have got no where with this at present I have searched the internet and found something here:

http://stackoverflow.com/questions/1117004/find-number-of-concurrent-users-in-a-sql-records

However I can not figure out the solution mentioned and have been unable to get it to work. If anyone has any thoughts on how best to do this I would be grateful

Thanks.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5411 Visits: 4639
Kwisatz78 (2/8/2013)
Hi all

I have a table which contains login and logout times for a large set of users, and we are wanting to know how to code it to pull back the maximum number of users who are logged on at any one time during that day.

I have got no where with this at present I have searched the internet and found something here:

http://stackoverflow.com/questions/1117004/find-number-of-concurrent-users-in-a-sql-records

However I can not figure out the solution mentioned and have been unable to get it to work. If anyone has any thoughts on how best to do this I would be grateful

Thanks.


One way would be to create a buckets table with one row representing each and every single minute of the day - is that granularity enough for you? - then, for each row on your login/logout table add 1 to all the buckets representing minutes the particular user was logged into the system.

At the end of the process just select the bucket with max() and the minute of the day represented by the winning bucket plus the value of the bucket would tell when the max() number of users was logged in and how many of them where logged in at the time.

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
drew.allen
drew.allen
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6280 Visits: 10871
The solution provided in your link is likely to be the fastest. (It essentially the same as what Paul is suggesting, but limiting the buckets to only the specific login times.) If you post what you have already tried and where you ran into problems, we can help you understand how it works.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86708 Visits: 41103
drew.allen (2/8/2013)
The solution provided in your link is likely to be the fastest. (It essentially the same as what Paul is suggesting, but limiting the buckets to only the specific login times.) If you post what you have already tried and where you ran into problems, we can help you understand how it works.


If you're talking about Alex K's solution, it's absolutely horrible. If you take a look at the Actual Execution Plan, it has a full blown accidental CROSS JOIN in it for smaller numbers of rows and a full blown Triangular Join in it for larger numbers. I wouldn't use that code if it was the only way to get this problem done.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15818 Visits: 11355
Kwisatz78 (2/8/2013)
I have a table which contains login and logout times for a large set of users, and we are wanting to know how to code it to pull back the maximum number of users who are logged on at any one time during that day.

This was the subject of a series of articles by Itzik Ben-Gan. The fastest solution found was submitted by, among others, our very own R Barry Young. You can read all about it here:

http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3-103407

Be sure to read the whole thing, not just the first page. I have a SQLCLR solution that beats that by around 30% but unless you really need that extra bit of speed (and are quite expert with T-SQL and SQLCLR) I would stick with Barry's code.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86708 Visits: 41103
SQL Kiwi (2/9/2013)
Kwisatz78 (2/8/2013)
I have a table which contains login and logout times for a large set of users, and we are wanting to know how to code it to pull back the maximum number of users who are logged on at any one time during that day.

This was the subject of a series of articles by Itzik Ben-Gan. The fastest solution found was submitted by, among others, our very own R Barry Young. You can read all about it here:

http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3-103407

Be sure to read the whole thing, not just the first page. I have a SQLCLR solution that beats that by around 30% but unless you really need that extra bit of speed (and are quite expert with T-SQL and SQLCLR) I would stick with Barry's code.


Freakin' awesome link, Paul. I was able to modify Barry's code to also correctly populate the MX column for the Logoffs so that I could graph the "valleys" as well as the "peaks". I've been trying to do this solution in a similar fashion and got seriously hooked because I just didn't see the 2:1 ratio that Barry included in his final formula. Thanks for posting the link. It's definitely a keeper.

Barry, if you read this post, I know it's been 3 years since you wrote the code and that article came out but thanks a million to you for writing the code and to Itzik for 'splainin' it.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15818 Visits: 11355
Jeff Moden (2/9/2013)
Freakin' awesome link, Paul. I was able to modify Barry's code to also correctly populate the MX column for the Logoffs so that I could graph the "valleys" as well as the "peaks". I've been trying to do this solution in a similar fashion and got seriously hooked because I just didn't see the 2:1 ratio that Barry included in his final formula. Thanks for posting the link. It's definitely a keeper.

Yes, it's very clever but quite simple at the same time, once the concepts sink in. Once SQL Server supports proper ordered aggregates, the problem will be trivial.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86708 Visits: 41103
I've worked with the "difference between ROW_NUMs" a lot and, like you say, once you've got the concept down, it's very simple. Heh... unless you did like I originaly did and miss the bloody 2:1 ratio that Barry used in his final calculation.

Thanks again, Paul.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86708 Visits: 41103
@Kwisatz78,

Are you all set now or do you need some additional help?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Kwisatz78
Kwisatz78
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1085 Visits: 1821
Crickey guys thanks very much for all the replies, I will delve properly into them tomorrow when back at work, I decided to give myself a weekend off this week and took some R&R, but will definitely post back if I get stuck further.

Thanks again
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search