Improving SQL Performance with Dynamic SQL

You can use dynamic SQL in some situations to simplify a SQL statement and gain performance:

begin-select
order_num
from orders, customers
where order.customer_num = customers.customer_num
and ($state = 'CA' and order_date > $start_date
     or $state != 'CA' and ship_date > $start_date)
end-select

In this example, a given value of $state, order_date, or ship_date is compared with $start_date. The OR operator in the condition makes such multiple comparisons possible. With most databases, an OR operator slows processing. It can cause the database to perform more work than necessary.

However, the same work can be done with a simpler select. For example, if $state is ‘CA,’ then the following select works:

begin-select
order_num
from orders, customers
where order.customer_num = customers.customer_num
and order_date > $start_date
end-select

Dynamic SQL enables you to check the value of $state and create the simpler condition:

if $state = 'CA'
   let $datecol = 'order_date'
else
   let $datecol = 'ship_date'
end-if
begin-select
order_num
from orders, customers
where order.customer_num = customers.customer_num
and [$datecol] > $start_date
end-select

The [$datecol] substitution variable substitutes the name of the column to be compared with $start_date. The select is simpler and no longer uses an OR operator. In most cases, this use of dynamic SQL improves performance.

See Using Dynamic SQL.