Subject Re: [firebird-support] Bind Variables and Performance
Author Helen Borrie
At 01:34 AM 10/07/2003 +0000, you wrote:
>I'm not sure if other people have seen this weblog before:
>Since it was mostly about comparing MySQL and Oracle, it was nice to see
>that Firebird was also included and that it was outperforming Oracle and
>was very close behind MySQL in cases (in the tests conducted on Windows 2000.)
>One can make of these figures what one will... it is only a
>weblog. However, I'm just wondering what he is talking about when he
>refers to 'bind variables' and performance in Firebird. Are these just a
>PHP thing? I've searched the docs and this group and cannot find any
>reference to 'bind variables'.

"Using bind variables is actually faster than sending straight SQL in
Oracle (formerly I said it was six times slower, but I found a bug in my
benchmark - turns out I was commiting on every insert)."

He is talking about parameterised queries. With Firebird, you can make a
statement like this:

WHERE ACOLUMN = ? (or :aColumn, or whatever is applicable in the dev.

(various syntaxes, depending on how the connectivity software implements

You prepare this query once and, thereafter, fire it off as many times as
you need to with a fresh set of parameter values, with minimal "transport

For example, in Delphi, applying new values to parameters is as simple as

ParamByName('AColumn').AsInteger := SomeInteger;
ParamByName('BColumn').AsString := SomeStringVar;

Respecifying the VALUES for the parameters doesn't require re-preparing.

Re-applying the entire query spec for each request is a very costly and
unnecessary expense - on the client, on the wire and on the server.