July 1, 2003 at 10:27 pm
I have a user table which is to store user's demographics. Every time a user signs up, one row will be created.
When this table grows (say, 50M rows now), it is taking more and more time to generate reports from that table (if that is the only way).
I am thinking a work-around for this but I am not very sure...I want the experts here to give me some inputs:
The basic idea is to keep the user table grow...seems like there is no other way to get around it..(no duplicate users allowed for that table)
But I have a sp to summarize the report for each day and insert it to a report table. So when I do the reporting I just need to look at the report table and no need to get that from the user table any more.
Let me know what you think.
Thank you,
TH
Edited by - huangting on 07/01/2003 10:27:39 PM
July 1, 2003 at 10:40 pm
Hi
Is it taking more time to insert/update/delete data than to query?
Back to your question, if indexed correctly, you shouldnt have too much of a problem with performance given the dynamics of a b-tree structure no matter he table size. I dont have a 50million row table to try some stats over, but with my 3million row obj i have virtually zero issues between it being 2mill to 3mill. What i have noticed is the time taken for index maintenance and general performance with large numbers of foreign key lookups etc on insert. In terms of reporting, what sort of reporting are you doing? ranged based over date? some other field?
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 1, 2003 at 11:17 pm
That's a good solution provided the existing data doesn't change. Also, from experience, you'll need some smarts in your summarization SP to look for days that may have been missed or re-summarize for a particular range of days.
If existing data does change then you may want to consider a trigger on the main to maintain the the consistency of the summarized data.
Another option, with a bunch of prerequisites, is an Indexed View. Numero Uno prerequisite is Enterprise Edition.
Cheers,
- Mark
July 2, 2003 at 7:58 am
Thank you both for your comments and suggestions.
I got the following suggestion from Microsoft newsgroup which I think is valuable and I want to share it here with you:
You can:
1) create the right indexes on your tables so that the lookups are fast on each one.
2) create indexed views that display the same columns as your daily report, and index it by date.
note: (2) is only useful in enterprise edition.
when you do your report query, it should read from the indexed view, making it pretty much instantaneous.
Is faster than sumarizing it yourself, and gives you database integrity and is always up to date, not just once a day.
By Zig Mandel
July 2, 2003 at 3:40 pm
Just a comment:
- indexed views DO NOT use the view's index IF the base table have good indexes on it.It will use the table indexes, so you earn zero.
Regards
lmt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply