March 3, 2003 at 6:47 pm
Hi everyone,
We have a table that links users against products that my company sells. Users trade these products actively (buy and sell) and thus generate profit/cost data from gains/loses from their purchases and trades. I guess the best analogy would be that it behaves like a stock trading site where people buy and sell stock and every item they own is always active.
Now here's the question. Since every item bought is "potentially" always active, how do I tackle performance down the road when the table reaches a couple of millions records? In every other module of our site we archive records effectively and performance is excellent, but in the case of this table I'm having a hard time thinking of how we are going to keep performance in a couple of years, or probably months. We have optimized everything that we can, indexes, select-update-insert statements, moved a lot of computations to back end processes (some thru ms-mq, some thru scheduled jobs) but I know eventually I'll have to face to bigger problem. How do the big financial houses do it? How do you guys would tackle this problem? We though of breaking this table into different ones, say user 1 to 1 million get table1, user 1 million 1 to 2 million gets table2, etc. but this doesn't seem an elegant and manageable solution.
I waiting to hear what the big guys do. And thanks in advance to all of you!
Cheers,
ifarfan
March 3, 2003 at 8:36 pm
I dont have super huge tables, but I have some with 2mil plus rows. Perf is fine. I think if you normalize and index, SQL works pretty well. Table scans are the things to avoid!
Andy
March 5, 2003 at 10:48 am
I agree that 2mil isn't a huge number, especially when you're probably querying for a single row most of the time for products. If not, then you should have some limiting factor and index on that.
You could break into "new products" and old products and use a view to join them. Still, without some mechanism to limit the queries you won't get benefits. If you have some type of "active" flag, then partitioned views can help here.
Steve Jones
March 6, 2003 at 8:19 am
We have a table that tracks the history of all our cases (about 210k). This table is currently 13.5 million rows, and growing at over 1% per day. We have no problems with performance, even on a relatively under-powered system (twin 800MHz CPU, 1GB RAM).
Good indexing is crucial. Monitor queries and see what the most common "WHERE" clause items are. Build appropriate indexes based on these results.
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply