I have an ASP.NET site I've inherited where all page views are logged to a database. Currently we store sessionguid, page, querystring and timestamp. We generally keep it at about 100M rows before purging. Certain users have access to pageview reports and it often times out. Other than doing an index analysis, any other tips on how to best handle this? Should I normalize out the page and/or querystring fields?
+1
A:
Have seperate tables for the total pages in your system and have a cross reference table between the pages table with users table. Perform only updates and update the indicators that you need in the reports. Are users registered on your site?
Did you try using the server log files for site statistics (Apache/IIS)? You should also plan to analyze the data in the access log to extract information about total request and page counts, bandwidth, popular pages, user paths, browser usage etc. There will be lot of information in server log files.
Also take a look at ganglia , maybe you can externalize this whole operation from your core system
CodeToGlory
2009-07-30 18:30:54
i was thinking about doing this but wasn't sure if the joins would add too much overhead. will have to do a test run to try it i guess. we track both logged in pageviews and anonymous pageviews
qntmfred
2009-07-30 18:33:36
i haven't done anything with the IIS logs yet. In the past I've used a few packages for static daily/weekly/monthly reporting, but never tried it for real-time reporting. I'd need to be able to hook into something like that from my asp.net code anyways.
qntmfred
2009-07-30 18:48:34
CodeToGlory
2009-07-30 19:09:55
A:
100M records is not a realistic amount of rows to do real-time reporting on. You're either going to have to narrow down your dataset quite a bit or run reports every so often that generates more manageable reporting data.
routeNpingme
2009-07-30 18:32:29
While I'm not a SQL expert, I was under the impression that modern databases should be able to handle such a scale if properly configured. I know this is very subjective, do you have any other sources to back up your statement? thanks
qntmfred
2009-07-31 15:24:06