Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Is there a fast way for SQL to do a Group By Count on items within a group? Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 9:38 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc

select top 15 count(*) as cnt, city from table
group by city
order by cnt desc

select top 15 count(*) as cnt, company from table
group by company
order by cnt desc

Can the above three queries be combined into one and still be fast, if so how?

What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.

Thanks
Post #1566756
Posted Thursday, May 1, 2014 9:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 12, 2014 4:00 PM
Points: 341, Visits: 751
check out Windowing functions. Something like:

select count(*) over (partition by city) citycount
, count(*) over (partition by state) statecount
...

Post #1566761
Posted Thursday, May 1, 2014 9:56 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
Thanks
Post #1566763
Posted Thursday, May 1, 2014 2:54 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 4,474, Visits: 6,407
isuckatsql (5/1/2014)
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc

select top 15 count(*) as cnt, city from table
group by city
order by cnt desc

select top 15 count(*) as cnt, company from table
group by company
order by cnt desc

Can the above three queries be combined into one and still be fast, if so how?

What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.

Thanks


I want to see your expected output. If New York City is the most populous city but California is the most populous state, how would a single output with the respective columns and counts portray that? NYC is in NY, not CA...


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1566858
Posted Friday, May 2, 2014 4:36 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
Kevin,

You are correct!

I am not sure on the best way to do this.

Based on the Microsoft Outlook scenario, i assume you would need one SP for each of the folders: Inbox, Sent, Junk.....etc.. which i'm sure would be horribly slow.

One of the suggestions made was to hold all the records in memory, then make the counts based on the records contained only in memory. Then update the records in memory every two hours.

Any other ideas?

Thanks
Ian

Post #1566981
Posted Friday, May 2, 2014 5:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 6,879, Visits: 13,460
isuckatsql (5/2/2014)
Kevin,

You are correct!

I am not sure on the best way to do this.

Based on the Microsoft Outlook scenario, i assume you would need one SP for each of the folders: Inbox, Sent, Junk.....etc.. which i'm sure would be horribly slow.

One of the suggestions made was to hold all the records in memory, then make the counts based on the records contained only in memory. Then update the records in memory every two hours.

Any other ideas?

Thanks
Ian



I'm not sure what this recommendation is based on...
Before SQL2014 you cannot really control what data will be hold in memory and for how long.
How would such a suggestion be implemented to work undereach and every condition? What SQL command would you use to "force" it into memory?

Or am I missing something here?




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1566990
Posted Friday, May 2, 2014 5:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
Have you thought about how the TOP(15) requirement will work? What if your user wants to see - and expand - a state node which isn't in the top 15?
Mimicing the Outlook menu system is fairly common. So long as you put a little thought into the data structure driving it, performance doesn't have to be an issue.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1566996
Posted Friday, May 2, 2014 12:56 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 4,474, Visits: 6,407
Lutz is correct, as usual. You cannot command SQL Server to "keep this table in memory all the time" like you want. However, it WILL stay in memory "all the time" if it is regularly referenced, which this type of dashboard aggregates probably will. So you do what you are suggesting - make one or more aggregate tables and refresh them on a period that is acceptable to the stake holders. To speed the refresh of said data I would keep track of the last "something" that was counted up through and use that as an indexed, seekable predicate in your query that computes the increments for your aggregates. There are various ways to accomplish this efficiently.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1567156
Posted Friday, May 2, 2014 2:03 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
Is it better to do this with SQL or C# ?

I have the table running in memory via C# and the initial load takes 52 seconds, then each count, as a test all cities in CA, takes 0.03 seconds.

Thanks
Post #1567171
Posted Friday, May 2, 2014 9:50 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 4,474, Visits: 6,407
Seems quite silly (and unscalable) to me to read all the data just to read all the data iteratively. :)

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1567206
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse