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)

Preferred Member

10+ Year Member

joined:May 8, 2002
votes: 0

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)

Full Member

10+ Year Member

joined:Nov 3, 2003
votes: 0

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)

Moderator from US 

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

joined:Dec 10, 2005
votes: 24

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)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
votes: 0

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.