Hi Ryan,

Hope you enjoyed the snow day. Lost power here for a while, but fortunately no damage.



On 3/13/2017 11:09 PM, Ryan Culpepper wrote:
On 03/13/2017 06:30 PM, George Neuner wrote:

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?

When I read this, it occurred to me that you and I have been talking at cross purposes because there are 2 distinct notions of "prepare": client side /prepare /and server side /PREPARE/ are unrelated and (mostly) not even associated.

So to put us both on the same page  [and educate anyone interested] ...

    ---
Unless a query is submitted using a SQL PREPARE command, then most DBMS will compile and plan AS IF the query were static text with inline arguments.^[1][2] The server typically then will cache the code and plan for some time in case an identical query (both text and arguments) is submitted later.

The SQL PREPARE command - which includes query text - compiles the query using indirection hooks for any parameters, in lieu of having the actual arguments available. [The compiled query is executed using the SQL EXECUTE command which supplies any needed arguments]. In most cases, a generic execution plan is generated based only on the code because specific argument values are unknown.

Some DBMS will simply continue to use a generic execution plan, others replan each time based on the specific arguments, and some change behavior depending on how often different arguments are supplied. The savings (if any) come mainly from avoiding recompilation of the query for each different set of arguments.

/PREPARE/d queries are not cached in the same way as are static queries. /PREPARE/d queries are never dropped automatically - they persist until explicitly dropped by the user/application, or until their owning session^[3] is terminated.


On the client side, the /prepare/ protocol step creates local data structures for associating arguments with query parameters - structures which can be kept and reused (perhaps binding different arguments). Client /prepare/d queries persist on the client until dropped, but this is separate from - and has no effect on - server side query caching^[4] . And except in the case of a SQL EXECUTE command, it has nothing to do with server /PREPARE/d queries.


[1] The compiler knows that arguments to be substituted for query parameters are not to be considered SQL code, and so the "expanded" query is guaranteed to be safe (for some definition) from SQL injection faults.

[2] The compiler does _not_ rewrite the query text ... "substitution" of arguments is in the compiled code.

[3] "Session" is not necessarily synonymous with "connection". In some DBMS, a "session" can consist of multiple simultaneous connections which can share any temporary objects - temp tables, prepared queries, etc. - that have been created.

[4] Exceptions are local "library" DBMS such as SQLite, in which the "client" and "server" are together in the same process and query data structures are (or can be) shared.
  ---


So when you [Ryan] say:

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

I now am assuming that you are talking about client side protocol data structures and NOT about any use of SQL PREPARE / EXECUTE commands.

The reason I want to be clear about it is because /PREPARE/d queries persist and consume server resources until the session ends. Long running applications that create but never drop them can run afoul of server limits and suffer anything from cascading errors, to hangups or hung sessions, or even server crashes.

Although the same is true of /prepare/d queries on the client side, it is far more acceptable to crash a client application than to incapacitate a multi-user server (though neither outcome is desirable).

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