On 03/13/2017 06:30 PM, George Neuner wrote:
Hi Ryan,

On 3/13/2017 5:43 PM, Ryan Culpepper wrote:

Racket's db library always prepares a statement before executing it,
even if there are no query parameters. When allowed, instead of
closing the prepared statement immediately after executing it, the
connection stores it in a (Racket-side, per-connection) cache. The
cache key is just the SQL string. If you use the same query string
with different parameters, you'll still get the cached prepared

To extend my previous example:

  > (query c "select $1::integer" 1)
  ** caching statement
  > (query c "select $1::integer" 2)
  ** using cached statement

On the other hand, if the query string is not identical, it misses the

Hmm.   Then what is the purpose of having separate (prepare ...)?

Explicit control, history (the statement cache is much newer), and reflection (eg prepared-statement-result-types).

And what happens if the unknowing user issues a PREPARE statement for the
query directly to the DBMS?

You mean like PostgreSQL's PREPARE/EXECUTE commands? Those should work fine too, as long as the statements are given names distinct from the obscure names chosen by the db library.

Then there is the issue that deliberately prepared queries are not
optimized as heavily as normal queries - which is the case in pretty
much every DBMS that exists.  Heavyweight queries that require maximum
performance are harmed by preparation.

In general, performing a query consists of three steps: Prepare, Bind, and Execute. When there's a "one-step" approach, it's just a convenience wrapper around those three steps. So I'd be very surprised if Prepare intrinsically slowed down a query.

Maybe you mean that *parameterization* inhibits the query planner, because query planning doesn't take the values of query parameters into account. That seems to depend on the backend. PostgreSQL did planning at Prepare time before version 9.2 and does it at Bind time since 9.2 ("typically", the docs say). I don't know about other systems. But in any case, parameterization is under the control of the user.

If that's not what you meant, can you elaborate?

Moreover, prepared queries are not cached in the normal sense ... they
persist until explicitly dropped, or until the connection is closed.
There are both per connection and global limits on the number of
prepared queries, and bad things can happen if these limits are
exceeded  [e.g., I've seen versions of Postgresql freeze and Sybase
crash in such circumstances].   A long running application using pool
connections could get into trouble.

The statement cache is limited to 20 statements by default, and the statement cache is only enabled within transactions and flushed on commit/rollback. Also, connections returned to a connection pool with an open transaction are disconnected rather than reused.


You received this message because you are subscribed to the Google Groups "Racket 
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to