Reading your section on Bind Parameters was the first time I've heard that this can affect performance, as heuristics cannot be used on the values. This can be positive if the result ends up being the same (as you're then not wasting your time comparing various execution plans), or negative if the various values you might pass in would greatly alter the plan.
My question is why are bind parameters not resolved before generating the execution plan? Obviously, they're resolved eventually and I fail to see any dependencies between parameter resolution and optimizer execution. What if I want to use bind parameters to prevent SQL injection, but I really want the optimizer to choose the best plan based on the value I'm passing in?
asked Sep 04 '12 at 23:49
First of all, the answer depends on the database.
PostgreSQL, for example, can do that. Jeff Davis wrote an article about that:
That refers to another article, "Smart Logic" which covers this problem as well. You should also have a look at the database overview at the bottom of this page. It has some hints hwo different database handle this problem:
However, the problem is rather unsolved. Notwithstanding that, there are some workarounds that vary from database to database.
ps.: sorry for the late response. Holidays :)
answered Sep 28 '12 at 15:37
Markus Winand ♦♦