I've recently created a shopping cart system that uses SQL Server on the backend. The shopping cart will add a record to a table called CartEvents each time anything happens, like view item, update, delete etc. anyhow, the site has been up for about a week now and that particular table has about 4200 records already, I project tens of thousands of records in that table, so how many records is too many records? Whats your experience with tables that have hundreds of thousands of records? How many records can SQL server handle and still perform well?
SQL Server will have no problems with millions of records as long as the database/tables are designed correctly. Will your purge/archive these records - any reason why you are tracking every action - what is really necessary ? My concern is when your site's traffic spikes and any dramatic growth - have you forecasted growth and what are your expectations ? Also, are you indexing the tables, if so what type and how many ? Also, what is your server configuration ?
The critical factors determining large table performance are physical storage architecture, indexation and clustering. In this case, and I've done too many grocery systems in my time, I'd suggest using two tables, one, very short, to hold active trolley data, a second, very large, to hold dead trolley data. Generally a trolley becomes dead when the shopper either buys the goods or wanders off for too long leaving the trolley abandoned.
Generally dead trolley data is of warehousing interest only. By keeping live data in a small table you can keep the store front running nice and fast.
To answer rnealejr, Im tracking most actions but not all. I use it as more of a real time statistics page than anything. As for the table, its just one very simple table that has a few columns. When I first wrote this message a few days ago the table had 4200 records and now has 6200, plus the web traffic seems to be going up by atleast 50 visitors per day, so I would say the table is going to be getting about 40-60,000 records per month in the near future. As far as indexing the table, I havent set up an index on it yet because Im not sure whats the best way to do it, is there a standard rule when setting up the index? or does how you interface and use the data going make a difference when setting it up?