Ledion B wrote:
> Hi all,
>
> I've been working on trying to get the
> PreparedStatement's performance out of mysql, but
> with not much luck.
>
> My testing environment
>
> A table with 100,000 rows (primary key (id)) id
> -> 1 - 100,000
> Issue 10,000 SELECTS for a random set of ids
>
> Measure the time it takes to complete
>
> Compare the time it takes for using
> PreparedStatement vs simple Statement. I am NOT
> seeing any performance improvement whatsoever.
>
> Server version: Mysql 4.1.10-standard
> Connector/J: mysql-connector-java-3.1.7.bin.jar
> OS: Linux
> Java: 1.4.1
>
> Connection String
> "jdbc:mysql:///test?user=XXXX&password=XXX" +
> "&emulateUnsupportedPstmts=false" +
> "&useServerPrepStmts=true" +
> "&cachePrepStmts=true";
>
> The PreparedStatement code
>
> testPrepared() {
> String query = "SELECT p.field1
> FROM perf_test2 as p WHERE p.field1 = ?";
[snip]
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).
Where it has performance wins is when you 1) Have a _lot_ of static data (read a few KB) compared to your parameters, or you have a lot of numeric/datetime parameters and/or columns returned.
I'm not sure you're going to see a lot of difference over 10000 executions on a single thread either. Where the performance differences really come into play the most is on highly loaded networks or with complex result sets with many columns.
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.
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html