Re: PreparedStatement performance issues
Posted by: Mark Matthews
Date: May 07, 2005 08:30AM

Ledion B wrote:
> Mark Matthews wrote:
> >
> > The prepared statement code in the server is
> our
> > first "cut" at implementing prepared
> statements.
> > As such, it doesn't cache execution plans, so
> as
> > you've seen in some cases it's no quicker
> than
> > normal statements (and is sometimes slower).
> Well, correct me if I am wrong. For a prepared
> statement all query parsing and verification is
> done only once, (is this part of the execution
> plan ?)

It is only done once, but you have a statement that is very short, so the overhead you are saving is very, very low in your testcase.

This parsing _does_not_ include the execution plan which is the optimization stage, so no, that part is not currently cached.

> > Also, Server-side prepared statements are not
> able
> > to take advantage of the query cache built
> into
> > the server, so if you have any reads that
> repeat,
> > those will end up being quite a bit quicker
> in
> > some cases if the table is read-only.
> I was just about to write about this, than when
> query caching is enabled the preparedstatement
> performs much worse than the statement. This
> brings me int a suggestion as to where is the
> cache being looked up. Does the cache simply maps
> a query string to result set before it is being
> parsed or right before the execution planning
> starts? I think the latter is more correct and
> would make cache available to prepared statements
> too.

Yes, the query cache just maps the query string to a copy of the results in the same form as they are sent "on-the-wire". Therefore it is very fast and efficient as not much overhead is needed to make it work.

To make it correctly work with prepared statements requires a more complicated caching scheme that may/or may not deliver performance gains due to having to reconstitute the query from the statement and its parameters, or alternatively employ a multi-level cache and hash the parameters per parameterized query. It is not currently on the roadmap to enable this functionality. This is also more of a server question, so you'll probably get a more detailed/better answer if you post to one of the server-performance-related forums.


Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools

Options: ReplyQuote

Written By
Re: PreparedStatement performance issues
May 07, 2005 08:30AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.