On 2008.04.16, Jade Rubick <[EMAIL PROTECTED]> wrote:
> Can someone explain why we need prepared statements? I thought by
> using bind variables, we avoid the SQL parse time (at least with
> Oracle, that's my understanding) if you're using the same SQL but with
> different values in your bind variables.
If the implementation of bind variables takes a list of lists, one per
row to execute, then perhaps you're okay.
However, I'm guessing this isn't the case. In the "naive"
implementation scenario, a SQL statement is prepared then executed with
bind variables to prevent SQL injection attacks, but does nothing for
performance: each time the query is invoked, the SQL is parsed, and then
the bind variables passed to the statement execution operation.
In order to take advantage of the "parse once into a prepared
statement," you'd need to be able to store a handle to that prepared
statement, and use and execute that once-prepared statement over and
over again.
This is why the common idiom seen is:
statement = prepare(SQL)
execute(statement, bind values)
...
execute(statement, bind values)
The statement is prepared once, but executed multiple times. If your
implementation doesn't allow/require you to pass along a statement
handle, the odds are good that it's re-parsing the statement every time,
just to pass the bind variables in. You benefit from the elimination of
SQL injection attacks (very important!) but not from the saving of
reducing time spent parsing the SQL.
Does this help explain things?
--
Dossy Shiobara | [EMAIL PROTECTED] | http://dossy.org/
Panoptic Computer Network | http://panoptic.com/
"He realized the fastest way to change is to laugh at your own
folly -- then you can let go and quickly move on." (p. 70)
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
