: Tuning WebServer Response - I have this sam e question on StackOverflow and I was advised to ask it here hoping for more information. Here is the question: I am in rather unfavorable situation.

@Samaraweera270

At its root, this is almost certainly a database problem. The first thing to check is the indexes on the tables. If the full-text search is fast and the SELECT is slow, then there are most likely JOINS being performed that could benefit from indexes, possibly even composite indexes.

You will need to profile the application (run a trace using SQL Profiler) and watch the Duration value for the queries. You can even show the query execution plan. The benefit of tuning this way is that you don't need access to the application source; you are just adding indexes to the database. Proper indexing will make all the difference in large datasets.

Vote Up Vote Down

@Pierce454

You can take steps to optimise the DB. For example, you can have full-text index fields which increases the reponse time for SQL search queries dramatically (particularly when it includes medium - large bodies of text).

Beyond that, You need to have a look at what the problem is. Without access to source code it's difficult but it seems to me that, if the speed is cripplingly slow, then it is probably not very efficient code. It may be making hundreds or even thousands of individual DB queries for small pieces of data instead of grouping them. If you have no ability to modify the source code then you can't do anything to change this though.

So start by looking at the DB structure and trying to make sure it's fully indexed etc. If it is still too slow then you need to either get access to the source code so that you can modify the software yourself or find a different product.

I hope this helps.

Indexing links:

Official Documentation

MySQL Performance Blog

MySQL Optimization

P.S. SQL searches are exremely fast - so trying to have IIS cache results wouldn't necessarily be very productive. If you have a well indexed database then it's probably due to the number of queries as opposed to the time taken to respond to each query. Remember that each new query made by the code has overheads - such as time to establish connection, read/write actions etc.

Vote Up Vote Down