Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Disk access times

Author  Topic 

token
Posting Yak Master

133 Posts

Posted - 2006-12-28 : 17:44:30
Hi,

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?

Happy new year guys!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-28 : 17:58:42
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!
Go to Top of Page

token
Posting Yak Master

133 Posts

Posted - 2006-12-28 : 22:29:44
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.
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-29 : 04:25:48
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).
Go to Top of Page
   

- Advertisement -