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
statement.
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
cache.
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.
Ryan
--
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.
