SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Disk access times
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

token
Posting Yak Master

United Kingdom
133 Posts

Posted - 12/28/2006 :  17:44:30  Show Profile
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
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 12/28/2006 :  17:58:42  Show Profile
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

United Kingdom
133 Posts

Posted - 12/28/2006 :  22:29:44  Show Profile
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.

Edited by - token on 12/28/2006 22:30:47
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 12/29/2006 :  04:25:48  Show Profile
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
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000