List:Internals« Previous MessageNext Message »
From:Rick James Date:May 15 2009 7:57pm
Subject:RE: A simplified implementation for prepared statements
View as plain text  
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
> 
> 
Thread
A simplified implementation for prepared statementsKonstantin Osipov15 May
  • Re: A simplified implementation for prepared statementsJoerg Bruehe15 May
  • Re: A simplified implementation for prepared statementsRoy Lyseng15 May
    • handler.cc:write_row methodNihal Dindar15 May
      • Re: handler.cc:write_row methodAndy Bennett16 May
    • Re: A simplified implementation for prepared statementsAlex Esterkin15 May
      • Re: A simplified implementation for prepared statementsRoy Lyseng15 May
        • RE: A simplified implementation for prepared statementsRick James15 May
  • Re: A simplified implementation for prepared statementsKonstantin Osipov15 May
Re: handler.cc:write_row methodDindar  Nihal16 May
  • Re: handler.cc:write_row methodAndy Bennett16 May