On 03/13/2017 04:56 PM, George Neuner wrote:


On 3/13/2017 3:41 PM, David Storrs wrote:
On Mon, Mar 13, 2017 at 2:04 PM, Ryan Culpepper <ry...@ccs.neu.edu
<mailto:ry...@ccs.neu.edu>> wrote:
If you are using `prepare` just for speed, it might help to know that
most base connections have an internal statement cache that maps SQL
strings to prepared statement objects. The cache is only used inside
of transactions, though, to avoid issues with concurrent schema changes.

You can check on statement caching by setting the #:debug? argument
when connecting. Aside from lots of other noise, queries will print
out whether they are using the statement cache. Here's an example:

  > (define c (dsn-connect 'pg #:debug? #t))
  ....
  > (start-transaction c)
  ....
  ** in managed transaction
  > (query c "select 1")
  ....
  ** caching statement
  ....
  > (query c "select 1")
  ** using cached statement
  ....

Could that replace your use of `prepare`?


Looks like inside a transaction all statements are prepared.  Cool.
If so, that's very nearly perfect -- it adds and removes all the
concerns that transactions always add/remove, but offhand I can't
think of a case where doing it in a transaction would be a problem.

[...]

I'm not sure exactly what  #:debug?  is showing you.   Ryan knows better
than I do, but if you haven't explicitly called "prepare", then I
suspect it is only the specific syntax of the cached query that can be
reused, and that if you change the query in any way - e.g., by passing
different arguments - then the cached version will not be used.

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.

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.

Reply via email to