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