homepage Welcome to WebmasterWorld Guest from 54.145.183.169
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
limit to number of tables in sql 2000?
musicales

10+ Year Member



 
Msg#: 3212096 posted 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

10+ Year Member



 
Msg#: 3212096 posted 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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3212096 posted 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

10+ Year Member



 
Msg#: 3212096 posted 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