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.
