Re: Bind Variables...

"mark" <mark_brehmen_at_yahoo.com> wrote in message news:fa4781e4.0111062210.d4f590d_at_posting.google.com...

[ snip advantages and disadvantages of bind variables, all of which I   agree with. ]

> As a good enough thumb rule, if you have a statement which executes
> once or very few times and response times are good, avoid bind
> variables.
>
> For repetitive operations, especially inserts, bind variables are an
> absolute must.

Most of the answers in this thread are correct, but this one sums it up best.

A huge proportion of database statements (whether it's 80%, 90% or 99.999999% ) will benefit from using bind variables and only a small number of rare, pathological statements will be adversely affected by them. So the advice to programmers should be to use bind variables habitually unless you come across a specific instance where they cause a problem, then use other methods (eg. hard-coded values, histograms etc.)

If you want some real statistics, take a look at my page:

   http://home.clara.net/dwotton/dba/java_insert.htm

which (amongst other things) shows that not using bind variables for a simple insert statement makes it over twice as long to execute. And this is for a very simple insert of only 4 columns. I expect the performance to decrease more if more complicated sql statements need to be reparsed.

A response degradation of 2x may not seem much, but if *all* your statements are taking twice as long as they should, your managers will soon be talking about buying new hardware, if you don't solve it by optimising your software.

Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.