15.3 Consider Literal SQL for Decision-Support Systems

We discussed the
benefits of
using bind variables previously. The use of bind variables is often
beneficial in terms of performance. However, there is a downside to
consider. Bind variables hide actual values from the optimizer. This
hiding of actual values can have negative performance implications,
especially in decision-support systems. For example, consider the
following statement:

SELECT * FROM customer WHERE region_id = :x

The optimizer can parse this statement, but it won't
be able to take into account the specific region being selected. If
90% of your customers were in region 5, then a full table scan would
likely be the most efficient approach when selecting those customers.
An index scan would probably be more efficient when selecting
customers in other regions. When you hardcode values into your SQL
statements, the cost-based optimizer (CBO) can look at histograms (a
type of statistic) and generate an execution plan that takes into
account the specific values you are supplying. When you use bind
variables, however, the optimizer generates an execution plan without
having a complete picture of the SQL statement. Such an execution
plan may or may not be the most efficient.

In Decision-Support Systems (DSS), it is very rare that multiple
users use the same query over and over. More typically, a handful of
users execute complex, different queries against a large database.
Since it is very rare that the SQL statements will be repetitive, the
parsing time saved by using bind variables will be negligible. At the
same time, since DSS applications run complex queries against large
databases, the time required to fetch the resulting data can be
significant. Therefore, it is important that the optimizer generate
the most efficient execution plan for the query. To help the
optimizer generate the best possible plan, provide the optimizer as
much information as you can, including the actual values of the
columns or variables. Therefore, in DSS applications, use literal SQL
statements with hardcoded values instead of bind variables.

Our earlier advice about using bind variables in
Online Transaction Processing (OLTP)
applications is still valid. In OLTP systems, multiple users all use
the same programs, and thus issue the same queries. The amount of
data returned per query is typically small. Thus, parse time is a
more significant performance factor than in DSS systems. When
developing OLTP applications, save parsing time and space in the
shared SQL area by using bind variables.