Re: SQL parsing efficiency

Sybrand Bakker wrote:
> If you would submit variant 1 with different hardcoded literals this
> would
> result in parsing each individual statement. This is one of the safest
> ways
> to kill performance and should be avoided as much as possible.
> Variant 2 should be parsed only once, as variant 3 should also.
>

I once observed an execution plan difference between hard coded values and using variables. Oracle parses something like

select * from t where c=?

when you use variables. The CBO now has to create an execution plan from this considerung the average value for the column c. When you use hard codes values, the optimizer can consider this very value for the execution plan.
So if much of the time is consumed during execution an not in parsing (i.e. long running queries which are not called very often) hard coded values may be more performant.
But this depents on the CBO, the selectivity of single column values an so on so the performance may be better for *certain* hard coded values whereas the use of variables *always* reduces the parsing time and the *average* performance is better.

Hope tha helps,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de