After posting Tuning JForum for JavaRanch, a couple people asked me offline about what I look for when tuning queries.

Before
The very first thing I do is identify the slow query and benchmark a “before” number.  This proves that there is something to optimize and lets me see if my tuning helped matters.  If I’m tuning a web app, this may start out as a slow page.  If I have a slow web page that uses multiple queries, I’ll add debug statements or use a profiler to see which one(s) are the slowest.  Ultimately by the time I am done with this step, I have a specific query that I want to tune.    I find this step important because it focuses the problem on something attainable.  “Tune one query” is a lot easier than “the website is slow.”  Granted once I’ve tuned the query I am likely to need to identify the next slow query and repeat the process.  But it’s steady progress.

During
Some “tuning opportunities” comes up repeatedly during query optimization.  Some common ones I’ve encountered (not necessarily in the order I would look for them):

• Missing index – The first thing I do with a slow query is run a database explain and look at the output.  My previous blog entry has an example.  Table/heap scans are a big red flag for a tuning opportunity.  I look at what fields are in the where clause and make sure there is an index matching.  If not, I’ll add one or adjust the query to take advantage of existing indexes.
• Inefficient query – Sometimes the explain plan shows nested queries where it isn’t necessary or expected.  If this is the case, I’ll rewrite the query to avoid the repetitive work.
• Returning too many fields – If a query has “select *” or returns a lot of fields, I’ll examine the calling code to make sure they are all used.  If not, I’ll adjust the select clause to only return the fields that are actually needed.  If the database is on a different machine than the application code, network traffic is being wasted transferring unneeded data.  On a local machine, it’s still unnecessary work to read data that the caller does not need.  The effect of this on a network is large though.  Suppose you return just one unneeded column.  A query returning 1000 records has now wasted a kilobyte of network traffic that could have been spent on data you really did want.
• Too many roundtrips – A lot of small queries isn’t necessarily better than a large one.  There are two reasons for this.  One is the time for network roundtrips.  Your work isn’t getting done while the application and database are waiting for communication to go back and forth.  If the queries can be merged or turned into a stored procedure, the application is likely to perform faster.  Sometimes merging helps overall.  For example, querying a table by primary key 1000 times is going to be slower than making 1000 separate requests to get a row.  If for no other reason than that the database is doing redundant work analyzing the query each time.
• Check the algorithm – Sometimes the algorithm in the application is just plain inefficient.  There’s not much to say here because you need knowledge of an application to identify cases like this.
• Can it be cached – Some queries just take time.  It may be a 100 millisecond query run many times a minute.  Or it may be a 1 second query run once every 5 minutes.   The easiest way to make it take less time is to cache the results.  After all, if the database doesn’t need to run the query, it will be faster.

Note: all of this advice assumes we are dealing with a huge table.  Of course if it wasn’t the query would likely not be slow in the first place.

After
How much did it help?  If my change made a significant improvement, I’ll look to see whether I have any other queries with the same problem.  I still gather before/after numbers for these.  It goes faster since I know a way of tuning it before I start though.