This is more of a general database query I think, but I am using SQL 2K5 and I have a table that contains thousands and thousands of products. Surprisingly its name is called PRODUCTS
When I search on my website for a product, it is taking a very long time for it to return my search. I can see the HDD activity on my webserver going crazy as it wades through all the products trying to find the one I want with its full specification and accessories. Fair enough there are a lot of products, but many sites such as dabs.com or other online retailer would have more products than me and their sites run smoothly.
Is there any way to improve disk access times? I cannot break the PRODUCTS table into smaller tables or anything like that. Would a simple CPU and RAM upgrade do the trick? Or is there a feature in SQL Server I could play with to tweak performance?
You haven't given any info about the table or the query that finds the product. Is the table indexed for example? Adding an index if you haven't created any would be a lot cheaper than more CPU or RAM!
Thank you snSQL! You were correct, I didn't have any indexes on many of the tables that PRODUCTS references. I'm sorry for my stupidity, but I am learning database development 'on the job' so this is the first time I have encountered indexes. I could not have a primary unique key on many of the tables because BIDS has a bad time importing data into tables with constraints. So a non-unique index was the solution.
Thanks snSQL again, happy new year to you! I hope one day I can return all the help I get on this forum. Chirag and many others have saved my life on so many occassions.
I could not have a primary unique key on many of the tables because BIDS has a bad time importing data into tables with constraints. So a non-unique index was the solution. Trust me that is not the solution! It seems like a solution now, but one day (about 5 minutes from now) you're really going to regret that! Take the time now to figure out how to get the import and other operations to work with the constraints, because once you have a ton of bad data it is way harder to fix than the hassle of making things work now (not to mention all the errors that will start appearing in your applications).