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!

asked Sep 04 '12 at 23:49

Hiawatha's gravatar image

Hiawatha
21225


2 Answers:

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%20Winand's gravatar image

Markus Winand ♦♦
93651120

Thanks! I also got some good information on Stack Overflow on this topic.

(Sep 28 '12 at 22:27) Hiawatha

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.

answered Sep 07 '13 at 14:38

nmaqsudov's gravatar image

nmaqsudov
12

edited Sep 07 '13 at 14:39