MySQL Bugs: #107228: performance problem in prepare stmtment

Bug #107228	performance problem in prepare stmtment
Submitted:	6 May 2022 7:48	Modified:	6 May 2022 11:14
Reporter:	hao chen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	8.0.25/8.0.27	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	jdbc connector, prepare statement

Description:
When using jdbc to connect to mysql, the performance of the prepared statement is far inferior to that of direct execution.

How to repeat:
Data prepare:
create table t1 (a varvchar(50));
insert into t1 values ('str1');
...
insert into t1 values ('str7000');
select count(1) from t1;
+----------+
| count(*) |
+----------+
|   381829 |
+----------+
select distinct a from t1;
....
7503 rows;

Java connection and execute:
jdbc url: "jdbc:mysql:loadbalance://xx.xx.xx.xx:xx/assetcloud?useServerPrepStmts=true&useCursorFetch=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull&connectTimeout=10000&failOverReadOnly=false&loadBalancePingTimeout=1000&allowMultiQueries=true&isConnectionLevel=true&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai"

origin sql stmt: select a from t1 where a in ('str1','str2','str3'....'str5000');   
prepare sql stmt: select a from t1 where a in ('?','?','?'....'?'); 

code:
...
ps.setString(1,"str1");
......(5000)
ps.setString(5000,"str5000");
PreparedStatement ps =connection.prepareStatement(stmt.toString());
Statement stmt = connection.createStatement();
ResultSet rs1 = ps.executeQuery();
...

It takes 120 seconds to execute the statement in the above way, but it only takes 1 seconds to execute the original statement directly.

For one thing,the executor call array->find_item(args[0]) in function Item_func_in::val_int() when we execute the original statement directly, then call the std::binary_search to search for the target string from array,the time complexity is O(logN),and for another,the array is null when we execute it use prepare and strings in 'IN' function cached in Item_cache,we need to read them from cache first and compare them one by one, the time complexity is O(N*N).In my test case above, the number of comparisons is 5000*380000, it wastes a lot of cpu resources.

In this case, can we try to optimize the code to improve the performance of prepare statment?

Hi,

The useServerPrepStmts=true is the reason for the performance issue. Use useServerPrepStmts=false if you want higher performance.

Thanks for taking interest in MySQL Server

Hi  MySQL Verification Team ,

I believe this limitation of the server-side prepared statements was fixed in the latest versions of Connector/J and MySQL Server.

hao chen,

can you try with the latest Connector/J? It may be related to a significant change in 8.0.22 on how the server prepares statements. In this case, client library needs to be aware of the change.