Reporting on massive amounts of data in real time: Need input
Since I've been in the content business online for eight years or so, advertising is the life line that pays the bills. Not my first choice since it has been relatively unstable since the start, but it is what it is.
Eventually the time came where I needed to develop my own ad serving application. The truth is, it works extremely well in terms of serving ads. Where it doesn't work that well is reporting from the millions upon millions of rows generated.
Here's the situation. Every time an ad is served, a row is recorded that contains it's ID, it's location (an ID for a specific ad position on a page), the time it was served, and two GUID's, one for the user's session and one for the user's lifetime identification. The last two are used for session and overall impression capping. They're indexed, and surprisingly enough inserts aren't hindered. I seem to recall an index on the ad's ID as well.
Reporting queries ask for total counts in a given time span, either by the ID or the location, and need to be displayed with hourly or daily totals. Originally I had a background thread that counts up totals every hour and puts those in a new table, but even querying that data is too slow.
Can anyone with more SQL experience offer any suggestions?