One of the most common causes for awful visible performance (as opposed to the common dismal performance of batch programs, which can often be hidden for a while) is the use of dynamically defined search criteria. In practice, such criteria are a consequence of the dreaded requirement to "let the user enter the search criteria as well as the sort order via a screen interface."
The usual symptoms displayed by this type of application is that many queries perform reasonably well, but that unfortunately from time to time a query that seems to be almost the same as a well-performing query happens to be very, very slow. And of course the problem is difficult to fix, since everything is so dynamic.
Dynamic-search applications are often designed as a two-step
drill-down query, as in Figure
8-2. Basically, a first screen is displayed to the user with a
large choice of criteria and an array of possible conditions such as
date between ... and .... These criteria are
used to dynamically build a query that returns a list with some
identifier and description, from which you can view all the associated
details by selecting one particular item in the list.
Figure 8-2. A typical multi-criteria search
When the same columns from the same tables are queried with varying search criteria, the key to success usually lays in a clever generation of SQL queries ...