In testing the new 7.3 prepared statement functionality I have come
across some findings that I cannot explain. I was testing using PREPARE
for a fairly complex sql statement that gets used frequently in my
applicaition. I used the timing information from:
show_parser_stats = true
show_planner_stats = true
show_executor_stats = true
The timing information showed that 60% of time was in the parse and
planning, and 40% was in the execute for the original statement. This
indicated that this statement was a good candidate for using the new
PREPARE functionality.
Now for the strange part. When looking at the execute timings as shown
by 'show_executor_stats' under three different senerios I see:
regular execute = 787ms (regular sql execution, not using prepare at
all)
prepare execute = 737ms (execution of a prepared statement via
EXECUTE with no bind variable, all values are hardcoded into the
prepared sql statement)
prepare/bind execute = 693ms (same as above, but using bind variables)
These results where consistent across multiple runs. I don't understand
why the timings for prepared statements would be less than for a regular
statement, and especially why using bind variables would be better than
without. I am concerned that prepared statements may be choosing a
different execution plan than non-prepared statements. But I am not
sure how to find out what the execution plan is for a prepared
statement, since EXPLAIN doesn't work for a prepared statement (i.e.
EXPLAIN EXECUTE <preparedStatementName>, doesn't work).
I like the fact that the timings are better in this particular case
(upto 12% better), but since I don't understand why that is, I am
concerned that under different circumstances they may be worse. Can
anyone shed some light on this?
thanks,
--Barry
