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