[DB-SIG] Re: db module wrapper

[Paul Moore]

Marc Colosimo <mcolosimo at mitre.org> writes:

> On Aug 20, 2004, at 10:34 AM, Andy Todd wrote:
>
>> Marc Colosimo wrote:
>>> On Aug 19, 2004, at 11:57 AM, M.-A. Lemburg wrote:
>>>> Marc Colosimo wrote:
>>>>
>> [snip]
>>>> After all binding
>>>> parameters exist to make all this quoting unnecessary and
>>>> indeed, many drivers pass the SQL string and the parameters
>>>> well separated to the database.
>>> not pgdb (_quoteparams) or cx_oracle (Cursor_SetBindVariables). I
>>> couldn't check psycopg because the links from freshmeat are dead.
>>
>> Call me Mr Picky, but that is not true for cx_Oracle. It uses a
>> feature of Oracle called bind variables. For an explanation see;
>>
>> http://www.rittman.net/archives/000832.html
>>
>> The function Cursor_SetBindVariables in Cursor.c of cx_Oracle simply
>> unpacks the values passed to the execute statement. Quoting and
>> other esoteric conversion requirements and performed by the Oracle
>> software.
>>
>
> Okay, I was wrong about BindVariables, which does something completely
> different than quoting ( I don't fully understand how cx_Oracle does
> it and at this time I really don't care or have time to look into).
> The way cx_Oracle handles things would throw a wrench into what I want

The key point with Oracle bind variables is that you pass a SQL
statement

    select col1, col2 from tab where col3 > :var1
    and col4 between :var2 and :var3

to the database, plus a series of parameters var1, var2, var3. The
database parses the SQL *once only* and reuses the parsed statement
whenever it can, *even if the values of the variables change*. This is
a massive performance bonus, as parsing is a serious overhead (in
Oracle, at least). Think of it as SQL + bind vars == function +
arguments. You compile the function once and call multiple times with
different arguments.

One of the biggest performance mistakes you can make when building an
Oracle system is to not use bind variables.

Paul.
-- 
Accomplishing the impossible means only that the boss will add it to
your regular duties. -- Doug Larson