From: Mike Campbell Date: 2003-01-03T00:57:48+09:00 Subject: Re: Ruby DBI > 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