[CONJ-684] Performance of con.getMetaData().getProcedureColumns(null, null, name, "%") is much lower than mysql jdbc Created: 2019-02-20  Updated: 2019-09-17

Status: Confirmed
Project: MariaDB Connector/J
Component/s: metadata
Affects Version/s: 1.7.4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Xiao Peipei Assignee: Diego Dupin
Resolution: Unresolved Votes: 0
Labels: mariadbjdbc, metadata, performance
Environment:

SUSE11

Issue Links:
Problem/Incident
is caused by MDEV-20609 Full table scan in INFORMATION_SCHEMA... Closed

 Description   

Recently, i use mariadb jdbc replace of mysql jdbc in our project. After replcing i found there is heavyer time costs of page reload in one of my forward app. To figure out why i has a below comparsion, i found it's the deal of the methond connection.getMetaData().getProcedureColumns().
in my case mariadb jdbc cost more 8 -10 times than MySQL jdbc.
Then i trace the source of mariadb jdbc i found that Mariadb JDBC actually queries the system table INFORMATION_SCHEMA.PARAMETERS to obtain a ResultSet describing stored procedures, which means that the performance of this method will gradually decline as the data volume of the system table increases.

 Comments   
Comment by Diego Dupin [ 2019-03-14 ]

Right, current implementation is fast only when not having lots of procedures/functions, and performance degrade fast when there is !

Implementation must rely on mysql.proc :
I_S.PARAMETERS view is a memory table construct from mysql.proc.
Difference is mysql.proc has index, permitting fastest access.

Downside is that I_S tables are always accessible without any rights, mysql.proc needs some rights. MySQL default implementation rely on accessing mysql.proc (or if not set option "noAccessToProcedureBodies")

Comment by Diego Dupin [ 2019-09-17 ]

This will be corrected by MDEV-20609, this being a better solution than relying on mysql schema that would need rights.

Generated at Thu Feb 08 03:17:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.