April 29, 2006 at 9:32 am
Hey All,
I started a new job a while back and one of the requests from management was to speed up the public search.
Initial research showed that some of the larger queries where taking as much as 30 seconds to run(including page load times), while only returning 2500 results, Not very impressive if you ask me considering that the main db server is a quad xeon with 4 gigs of ram.
Not to mention that the CPU usage was at a minimum of 85% for at least 15 hours a day with 100% spikes lasting for 2-4 seconds at times.
I have done all that I can think of to improve performance.
Reindex tables using profiler and the index tuning wizard.
The CPU usage is now down to an average of 45% for the same duration of time and minimal spikes.
Load page times are way down, nothing that I have found to be in excess of 10 seconds at peak times with more than 1000 active users on.
Average page load times are now under 5 seconds.
I have also optimized as best that I can think of the asp side of things as well.
My question is, what else can I do to speed performance. I know I can squeak more performance out of this and I am hoping to get some of the smaller page loads down to under 2 seconds.
Could it just be that we need more/better hardware? I have proposed this but didnt get the favorable attention that I think it deserved.
Keep in mind that most of the queries are inline/embedded, the reason for this, firstly is that, this is how I came into it, and most of the other developers are totally against stored procedures for some reason, another reason why is that most of the larger queries *MIGHT* require up to 15 params and I have yet to find a way to create a stored procedure that is quicker than the inline/embedded sql that I rewrote. Dont get me wrong I prefer stored procedures and have attempted to rewrite it using SP's but just couldnt match the speed for some reason.......
I am considering proposing some type of caching system to management. Any ideas? Thoughts?
Any advise would be appreciated.
May 1, 2006 at 5:19 am
If you are not using stored procedures, then it is very hard to confirm that the optimiser is using the indices the way that you intend. Stored procedures will improve the consistency of the optimiser.
"Raw" SQL needs to be compiled each time it is run, so there will always be an overhead if you do not use stored procedures.
Have you confirmed that all the columns in the WHERE clause of the queries is in an index? I have found that leaving out what appears to be a relatively unselective column from an index can have a surprisingly large impact on the performance.
You could try breaking down the queries to simpler ones and checking the way that the optimiser and the query path change when you add new tables and additional columns.
I hope that helps.
Regards,
Leo
May 1, 2006 at 2:39 pm
.........
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply