homepage Welcome to WebmasterWorld Guest from 54.161.240.10
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
limit to number of tables in sql 2000?
musicales




msg:3212098
 2:27 pm on Jan 8, 2007 (gmt 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?

 

stajer




msg:3212271
 5:34 pm on Jan 8, 2007 (gmt 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.

LifeinAsia




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

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)?

syber




msg:3213781
 7:46 pm on Jan 9, 2007 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved