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.

There's a difference between "prepared" and "compiled".

Every query is compiled. The compiled version can be - and typically is in most DBMS - cached for reuse if the same *identical* query string is presented again. With a normal query, the compiled version is optimized based on the specific values contained in the statement.

Prepare is different. The idea of "preparation" is to be able to reuse a generic query with different arguments. A prepared query is compiled to use variables rather than inline values, so it is not as well optimized as compilation of a normal query. The savings in preparation comes mainly from avoiding compiles on subsequent reuse.

The compiled version of a prepared query persists until explicitly dropped or the connection is closed. It is not cached in the same way as are normal queries.


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.


George

--
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