Question

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?

Thanks!

Answer 1

First of all, the answer depends on the database.

PostgreSQL, for example, can do that. Jeff Davis wrote an article about that:

http://thoughts.j-davis.com/2011/07/09/building-sql-strings-dynamically-in-2011/

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:

http://use-the-index-luke.com/sql/where-clause/obfuscation/smart-logic#smart_logic_affected

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 :)

Answer 2

I can explain this regarding to Oracle. RDBMS has the operator cache for recently compiled oprators. E.g. you send 10000 operators like this:

INSERT INTO t VALUES('A',5);
INSERT INTO t VALUES('AAA',6);
INSERT INTO t VALUES('d',1225);
...

It compiles 10000 queries, builds 10000 execution plans and open and close 10000 different cursors. Otherwise if you send

INSERT INTO t VALUES(:1,:2);

with different bound values for :1 and :2 Oracle compiles the query only once and reuses the same cursor again and again.