Jason Winnebeck wrote:
> In the bug comment you mentioned memory leak
> issues. I can possibly think of a case where you
> want to store only part of a string, but with
> substring you will always keep the much larger
> string in memory, so if you want to force a
> compaction, you must use toCharArray. Is that
> what you are thinking about when you say memory
> leak? If not, I am interested to know as to avoid
> similar things in my applications.
>
> I only checked on this out of curiousity because
> of the time it takes to prepare. One 3rd party I
> used had a "bug" where a debugging string
> generation was left in but unused, and was in a
> tight loop. Removing the code increased
> performance by something like 90%, which
> represented something around 25-50% of app time (I
> know, an extreme case -- but as this was for an
> embedded Java system it was impressive). My
> motivation of examining the code was simply to see
> if the call actually reduced performance a bit or
> was more superficial in terms of actual gains.
>
> We prepare a whole load of statements at the start
> of our application, because we have a large number
> of variations of where clauses. One thing why
> this probably does not matter is because of the
> round-trip time to server to prepare the statement
> is too large compared to any time spent processing
> on the client (I assume preparing causes network
> communication?). The problem is amplified by the
> fact that we have multiple connections.
>
> This is no huge deal, but with the new MySQL it
> takes maybe 5 to 10 seconds for our app to start
> now, most of that time spent in serverPrepare().
> We didn't notice this on MySQL 4.0 and 3.0
> connector because everything was done client side.
> We realize now the problem is important because
> of the load on the server to hold so many
> prepares. We've had three ideas that we haven't
> gotten around to yet:
>
> * Combine multiple SQL into one SQL, and set where
> clauses that are unused to some "identity"
> condition that picks all rows, and put all
> possible wheres into a single statement.
> * Many statements are never used on a connection.
> We can do a lazy prepare of the statements.
> * Change our pooling from connection pooling to
> statement pooling. That is, we "check out"
> statemetns rather than connections, to reduce the
> amount of prepares.
>
Jason,
The code in PreparedStatement.ParseInfo isn't used when you server-side prepare, so you're running into network latency issues, not anything to do with how well the constructor to ParseInfo() is implemented.
Are you using a custom connection pool? Most "standard" ones will do statement pooling for you.
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html