Does an ANSI-standard Prepare need to do anything more than hang onto
the SQL until the Execute?
Prepare has a downside: When a different value is bound to a "?", the
optimal query plan may change. I have seen as many as 4 different
EXPLAIN plans in MySQL; generally they made sense based on the
variations in constants being used.
Rick James
MySQL Geeks - Consulting & Review
> -----Original Message-----
> From: Roy.Lyseng@stripped [mailto:Roy.Lyseng@stripped]
> Sent: Friday, May 15, 2009 11:57 AM
> To: Alex Esterkin
> Cc: Konstantin Osipov; internals@stripped
> Subject: Re: A simplified implementation for prepared statements
>
>
>
> Alex Esterkin wrote:
> > I would advise to differentiate between "prepared statements" and
> > "cached/precompiled statements".
> >
> > Statement caching is a performance optimization that all
> databases use.
> > If a client submits a query, e.g., SELECT * FROM foo WHERE
> bar=2, and
> > then another similar query, SELECT * FROM foo WHERE bar=5, then
> > prepared statement cache allows treating the parsed, optimized, and
> > compiled statement as a parametrized one and re-executing it with a
> > new bound parameter value.
> >
> > Beside this, there is a "Prepared Statements" feature, which is 100%
> > defined by the ANSI SQL Standard. This is NOT subject to
> > interpretation. Take the ANSI SQL-92 Standard, read it, and comply
> > with what it says.
> >
> > There is another "prepared statement" semantic meaning/use
> - HA transactions.
> >
> > In terms of statement caching, only expensive to compile
> and optimize
> > statements are worthy of caching. Certain DDL statements may fall
> > into this category. For example, suppose every 10 seconds
> you create
> > a table named as "weblog" concatenated with timestamp:
> CREATE TABLE
> > %tname AS (SELECT ... FROM binlog, translog, clickdump, users WHERE
> > mytimestamp BETWEEN %ts AND (%ts + 10sec) AND............).
> You may
> > want to reuse the "SELECT" part for this, even though this is a DDL
> > statement.
> >
> > While these three feature sets may partially share internal
> > implementations, they are distinctly and fundamentally different,
> > especially when you consider all the related processing that has to
> > happen, such as privileges/security aspects, related statements
> > (FETCH, ...), etc. In MySQL, the three aspects are intertwined
> > internally, making it difficult to evolve. Perhaps, this is why the
> > Drizzle community has decided to rewrite rather than adapt these
> > features.
> >
> > About how other databases deal with this.
> > Oracle has a statement cache feature. It is described in detail in
> > the Concepts manual. I believe, Oracle statement cache
> implementation
> > is distinctly separate from prepared statement and prepared
> > transaction implementation.
> > Postgres uses statement naming. Cached statements are the
> statements
> > that either the user or the server decides to name. Implicit
> > statement caching in Postgres works on a per-session basis.
> Explicit
> > prepared statements are implemented identically to caching, EXCEPT a
> > different type of memory context is used for saving the statements.
> > Unnamed statements are also cached, but the next
> non-matching unnamed
> > statement overwrites the cached version.
> >
> > In your messages, you are discussing "prepared statements"
> in terms of
> > statement cache.
>
> I was referring to prepared statements as defined by SQL-92 and the
> sequels. Prepared statements is a per-session feature and completely
> controlled by the programmer.
>
> But we also implemented client-side JDBC statement pooling or
> caching by
> utilizing the server-side prepared statement feature...
>
> Thanks,
> Roy
>
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:
> http://lists.mysql.com/internals?unsub=1
>
>
