Click here to monitor SSC
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
SSChasing Mays
SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)

Group: General Forum Members
Points: 617 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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3095 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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2673 Visits: 9944
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45329 Visits: 39936
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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

Group: General Forum Members
Points: 10352 Visits: 11350
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45329 Visits: 39936
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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

Group: General Forum Members
Points: 10352 Visits: 11350
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45329 Visits: 39936
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45329 Visits: 39936
@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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Kwisatz78
Kwisatz78
SSChasing Mays
SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)

Group: General Forum Members
Points: 617 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