Re: 3.1.7 Not Using Server-Side Prep Stmt?
Posted by: Mark Matthews
Date: February 25, 2005 04:23PM

Jason Winnebeck wrote:
> I'll give you a more in-depth response on Monday
> as I don't have the code with me now, but our
> queries are in the form "SELECT a, b, c FROM T1
> WHERE X UNION SELECT a, b, c FROM T2 WHERE X"
> where X is some where statement on primary key.
> T1 and T2 have the same table definition. We used
> to prepare all statements whent he program starts,
> now we have a statement cache and we
> "lazily"prepare on first use as we don't always
> use all prep stmts. We use the same code for SQL
> Server JDBC which has a very long prepare time so
> we are aware of how to use them correctly -- prep
> once, execute many.
>
> 3.0.11 works identically in performance to 3.1.6
> and 3.1.7 with default settings. Setting
> emulatePS to off in 3.1.7 results in a 30%
> speedup.

Jason,

I've tested this locally here, and can only see a 2-3% difference between turning 'emulateUnsupportedPstmts' on or off, and that's probably safe to say is in the noise of being on my workstation which doesn't just run benchmarks in isolation.

The only thing I can think of is that the queries you have prepared make the driver think that it can't use server-side prepared statements, and the only SELECT queries that would do that have the word "LIMIT" in them. We didn't take the time to write a full-fledged parser because it would be slow, so maybe you're running into that kind of issue?

The code that detects whether a prepared statement can be done on the server side is this, maybe something will jump out at you?

private static boolean canHandleAsServerPreparedStatement(String sql) {
if (sql == null || sql.length() == 0) {
return true;
}

if (StringUtils.startsWithIgnoreCaseAndWs(sql, "SELECT") ||
StringUtils.startsWithIgnoreCaseAndWs(sql, "DELETE") ||
StringUtils.startsWithIgnoreCaseAndWs(sql, "INSERT") ||
StringUtils.startsWithIgnoreCaseAndWs(sql, "UPDATE") ||
StringUtils.startsWithIgnoreCaseAndWs(sql, "REPLACE")) {
// check for limit

return (StringUtils.indexOfIgnoreCase(sql, "LIMIT ") == -1);
} else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "CREATE TABLE")) {
return true;
} else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "DO")) {
return true;
} else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "SET")) {
return true;
}

return false;
}

-Mark

Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html

Options: ReplyQuote


Subject
Written By
Posted
Re: 3.1.7 Not Using Server-Side Prep Stmt?
February 25, 2005 04:23PM


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.