Counts Per Hour

Last Post 28 Dec 2006 08:45 AM by rambuyer. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rambuyer
New Member
New Member

--
27 Dec 2006 02:58 PM
Hello,

I am working on a report for a very large table (100 Million+ Rows). One of the columns is a datetime column. I need to generate counts per hour. Does anyone have suggestions for efficient ways to get the max rowcounts for the table. I need the top 100 busiest hours (where busiest means the most rows during that hour timestamp, and an hour is using clock-hours).

Any thoughts?

Thanks,

Jeff
JHunter
New Member
New Member

--
27 Dec 2006 03:48 PM
have you thought about using anaylsis services - this is exactly the kind of thing you'd use a cube for.

Jamie
rambuyer
New Member
New Member

--
27 Dec 2006 04:00 PM
Thank you for the suggestions.

I don't believe the datepart using hh will work as it would provide a "group by" value for each day for a given hour slot and this would be cumulatively. I am trying to find the "busiest" hour (most rows in that hour) as well as the average hour. I thought about the datepart, but it seems I would need to group it by mm + dd + yy + hh to achieve this.

It seems that this might be a common problem with the combined date and time values the way SQL Server stores them.

The analysis services sounds good, but I was hoping to run this type of report at a half-dozen client sites around the world, and it doesn't seem as pheasible to try to go this route for this many databases remotely.

Thanks again for the suggestions.

-- Jeff
rambuyer
New Member
New Member

--
28 Dec 2006 08:45 AM
Thanks for the suggestions. Perhaps I wasn't clear enough. Rather than looking for the busiest hour by counting every day and combining them. I was looking for the busiest one hour ever.

If you just use datepart in your group by, you are dividing the data into 24 hour parts and then if you are performing a count, it will be combining all of the individual hour totals. This would tell you which hours are cumulatively the busiest. I am hoping to go one step further and find the 1 hour that was absolutely busiest in any 24 hour period. Thus instead of just grouping by Hour, I need to group by hour and by day.

Any thoughts?
rambuyer
New Member
New Member

--
28 Dec 2006 11:03 AM
Thanks for the suggestions. Datepart was truly the way to go. I can run it on a table with 20 Million rows and generate Avg, min, max in under a minute.

Thank you for the suggestions. I will look into Analysis Services too.

Jeff
You are not authorized to post a reply.

Acceptable Use Policy