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.
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.