Thursday, February 09, 2012

unprepared queries vs statement caching

Racket’s db library supports parameterized queries where the SQL is given as a string (that is, unprepared):

(query-list c "SELECT field1 FROM table WHERE field2 = ?" 17)

This is handled by an implicit call to prepare, which turns the string into a prepared statement; the prepared statement is then executed with the arguments.

The problem with this approach is that it involves two trips to the server: one to prepare and one to execute. One trip would be better. In this post I discuss two techniques for eliminating extraneous server trips. I’ve recently added one of them to Racket; the other turns out to be problematic.