On 3/15/17 12:41 AM, George Neuner wrote:
Hi Ryan,
Hope you enjoyed the snow day. Lost power here for a while, but
fortunately no damage.
I did :) My neighborhood didn't get much snow by volume, but what it did
get was then rained/sleeted nearly into ice.
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.
I disagree with parts of this. I would label the two notions as
"client-originating prepare" and "SQL-originating PREPARE". But both of
them involve allocating a resource on the server, and both allocate
nearly the same kind of resource.
For PostgreSQL and MySQL, "client prepare" involves sending a message
(Parse and COM_STMT_PREPARE, respectively) to the server containing the
query string. The server parses, checks, and maybe plans the query and
stores it on the server under some identifier (in PostgreSQL, the client
chooses; in MySQL, the server chooses). On the client side, a prepared
statement object just wraps that identifier (along with parameter and
result metadata obtained from the server). On the server side, the
prepared statements show up in the relevant reflection tables
(pg_prepared_statements [1] and prepared_statement_instances [2],
respectively), just like those created with "SQL PREPARE", but there are
fields that distinguish the two kinds.
For sqlite3, as you said, the client/server distinction is irrelevant.
For ODBC, the behavior is driver-dependent. A driver could possibly
simulate prepared statements using only client-side resources, but it
would be a lot of work to support the inspection features like
SQLDescribeCol, etc.
[1]
https://www.postgresql.org/docs/current/static/view-pg-prepared-statements.html
[2]
https://dev.mysql.com/doc/refman/5.7/en/prepared-statements-instances-table.html
So to put us both on the same page [and educate anyone interested] ...
[... snipped pragmatics of PREPARE ...]
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.
I would say the two notions of prepared statement have different APIs
and possibly different pragmatics (eg planning and caching), but they
are both references to resources on the database back end.
---
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).
I am talking about client prepare, not SQL PREPARE. But client prepare
involves resources on the server too, and I'm not sure how that affects
your comments here.
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.
