> really think prep'd statements are more efficient? do they 
> really do anything special? i thought they just did a gsub before sending
off 
> the sql, nothing more.


It depends on the driver and the db.   Sometimes it's nothing more than a
gsub internally (by the driver, generally), sometimes the driver or db
creates a stored procedure, sometimes someting else entirely.

For one thing, a prep'd statement doesn't change with the data, since the
variable bits are represented by static "?"'s, so the driver/db can cache
that part, and/or cache the internally compiled query plan, etc.

Too, (again depending on the driver), when you use prepared statements
you're generally sending the SQL across the wire *once*, and the data
elements multiple times, reducing network chatter.

Lastly, in a prep'd statement, the data gets sent in a "closer to native"
format, rather than stringifying everything.


I do java professionally, and when we switched from BEA's WebLogic JDBC
driver which did the "gsub" scheme to a driver that did prep'd statements
the RIGHT way, we saw a 5X improvement.  When we turned on the driver-side
statement caching, we saw another 2X improvement!  (Total 10X).  That is to
say, our timing tests showed that a chunk of SQL that took 100ms to execute
"normally" took 20ms with prep'd statements, and 10ms with prep'd/cached
statements.

--
http://mcampbell.dhcp.norc.s1.com/

If you can't write it down in English, you can't code it. -- Peter Halpern