Bind. Parameters. Now.

by Daniel Berger

At RailsConf 2007 DHH mentioned that Rails 2.0 would support query caching on the client side in order to speed up AR. I immediately thought to myself, "Huh? Why do it on the client side when the database server will handle that?".


Michael Schoen
2007-05-23 10:08:36
Note that recent versions of Rails use an Oracle db setting for cursor sharing that effectively emulates bind variables. It's not as good as real bind variables, but performance tests indicate that it's nearly so. Agreed that true support is better, and a bunch of folks are working on that.

Also, I've made some local changes to support the use of Oracle hints, I can probably pull that into a patch for core.

Finally, note that the query caching DHH talked about is still FAR superior (in those cases in which it applies) to any sort of db caching. Not hitting the db will always trump having to hit the db, even if the db's got the same data cached.

Eric Hodel
2007-05-23 11:22:16
The query cache coming in Rails 2 (as of rev 6202) isn't a very good idea for sites running more than a moderate number of Rails processes since it wastes memory. memcached should be used for instead, so you get the largest cache size possible.
2007-05-23 13:38:31
The MySQL query cache is completely unrelated to prepared statements. There's some effort underway for 5.1/6.0 (I forget which) to make the cache usable by prepared statements, but the basic principle of MySQL's query cache is this: if the bytes of an incoming SQL SELECT match exactly a cached SELECT, the last output is substituted. That's all. And that's why writes nuke the cache: data in the changed table may not match the cached result sets anymore.
Robert Thau
2007-05-23 20:25:15
Hi. I guess I'm another person working on getting bind variables working. Following up on some conversations at RailsConf, I figured I'd have a shot at seeing what could be done without refactoring Active Record. FWIW, I've got an experimental patch for improving bindvar handling in Oracle. It's very fresh code, and incomplete (doesn't handle NULL or date values), but it does pass Active Record unit tests, so it might be fun for those inclined to play with such things.

(Unfortunately, doing this strictly at the adapter level requires a slightly dirty trick. If the adapter's quote method returns invalid SQL, it turns out that the only thing that will notice is the adapter's own execute method, which then has a chance to fix it up. So quote can leave a trail of bread crumbs in the generated "SQL" which execute can use to find the places where a quoted value got substituted in. It's not pretty, but at least in the AR unit tests, it doesn't blow up...)

2007-05-24 01:16:20
I think DB2 dose support bind variables/ query caching. I get the impression it makes quite a difference to performance.
2007-05-25 08:10:40
Bind variables were the number 1 performance improvement to be made in most applications that I've seen (sad as that is). One of my favorite consulting tricks was to get current performance statistics, get the top 10 queries not using bind variables, change a couple, and rerun statistics. I've seen orders of magnitude of change in the past (at one place changing their 2 worst queries decreased load from a 22 on average (8 proc box) to a 3 and increased query throughput by over 4 x. Actually this was the first shortcoming I saw with AR as most apps I've been pulled in to consult on didn't make use of these properly (and it was the quick and easy way to "show value").
Giles Morant
2007-05-25 08:50:57
DB2 (mainframe and UDB) and SQL Server definitely use bound parameters.
I think a database without them is unable to claim being a database.
Having said this, I was under the impression AR used bound parameters for PostgreSQL- I am surprised it does not.
2007-05-25 09:16:00
I think that bind parameters are essential. If Rails is going to compete effectively in the web app arena, it needs to make use of every performance advantage it can. This is a big one.
Mar Johnson
2008-02-12 14:37:53

Oracle and bind variables... in tests like this the test code with bind variables is always 2-3 times faster. But... make the test code multi-threaded, and run it against oracle on a 4 or more CPU server. (hint - entry level Sun Sparc servers now come with effectively 32 CPUs!) Due to latches in the statement cache in Oracle forcing massive concurrency contention, the proper use of bind variables in Oracle can easily make a 10x or more difference in system capacity. Ie, use bind variables and handle 10x more users on the same db with the same page response time.