You can create the niftiest application in the world, but if access to back-end database servers creates a bottleneck, your end-users or customers won’t be happy. So fine-tune those database queries and maximize performance.
Three basic measures can help you improve query performance. First, most database products include tools (such as DB2 UDB for iSeries’ Visual Explain) that can dissect your query during development, providing feedback on syntax and the approximate timing of the various sections of the SQL statements. Using this information, locate the lengthiest portions of the query and break those down further to see how you might shorten the execution time. Some database products also include performance advice tools, like Oracle’s Automatic Database Diagnostic Monitor, that provide recommendations (such as suggesting you create a new index) to speed up queries.
Next, turn on database monitoring tools on a staging server. You might use a third-party monitoring product, such as Fidelia’s NetVigil, if your database lacks monitoring support. With the monitors enabled, generate traffic against the database server using load-testing scripts. Examine the data gathered to see how your queries performed while under load; this information may lead you to some further query tweaking.
If you have enough server resources to mimic your mixed workload production environment fairly closely, you can execute a third round of query tuning using a load testing tool, such as OpenSTA, plus database monitoring to see how your queries perform alongside other applications that hit the database.
As database conditions change -- with volume growth, record deletions, and so on -- keep testing and tuning. It’s often well worth the effort.