Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

limit to number of tables in sql 2000?



2:27 pm on Jan 8, 2007 (gmt 0)

10+ Year Member

I've been trying to do a 'customers who viewed this item also viewed' in sql2000 - so far I've stored every session and product id in one big table, but it's got so big that queries take forever, so I'm wondering - could I create 10,000 tables, one for each product, and then simply query each much shorted table for each individual product - is there any overhead or problem in creating so many tables?


5:34 pm on Jan 8, 2007 (gmt 0)

10+ Year Member

That will decrease performance further. You best bet is to periodically aggregate the data and store it with the product.

For example, once a week you run a script that goes through the sessions and products and finds the common products for each product. Store that info with the product, delete the sessions/product info.


5:52 pm on Jan 8, 2007 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

but it's got so big that queries take forever

One thought: Is your table properly indexed? And if so, how long has it been since you rebuilt the index(es)?


7:46 pm on Jan 9, 2007 (gmt 0)

10+ Year Member

You can have over 2 billion tables per database in SQL Server 2000. Creating extra tables itself would not necessarily increase your overhead. If a few products account for most of your queries, then having them in their own table could be a big benefit. You could take it a step further and pin the most used tables to memory (DBCC PINTABLE). Before trying any of this howerver, you should make sure you are taking maximum advantage indexes on your current table.

Featured Threads

Hot Threads This Week

Hot Threads This Month