maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Dušan!

It's now reported as a bug, see https://jira.mariadb.org/browse/ODBC-313

Note, that you can add yourself to the watch list to get notified about
this issue changes.

Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx

On Apr 30, Dušan Pavlica wrote:
> Hi,
> 
> I'm new with MariaDB. We try to switch from MySQL 5.0.96 to MariaDB 
> 10.5.9. Move data to MariaDB was easy but now we have problems with 
> speed of ODBC connector. I tried MariaDB ODBC connector 3.1.12 with 
> default settings without SSL and our applications (mostly written in 
> Embarcadero RAD Studio) showed very poor performance. When I tried MySQL 
> ODBC connector 8.0.24 everything ran smoothly. Queries through MariaDB's 
> connector was 3-5 times slower. In general query log I found that 
> MariaDB's connector generates a lot of queries to 
> information_scheme.columns table and all over again it reads variable 
> TX_ISOLATION.
> 
> Short extract - MariaDB connector:
> 
>               3 Query    SELECT VARIABLE_VALUE FROM 
> INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='TX_ISOLATION'
>               3 Prepare    select * from stanice
>               3 Execute    select * from stanice
>               3 Prepare    SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS 
> TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 
> 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 
> 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice'  
> ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
>               3 Execute    SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS 
> TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 
> 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 
> 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice'  
> ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
>               3 Close stmt
>               3 Reset stmt
>               3 Query    SELECT VARIABLE_VALUE FROM 
> INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='TX_ISOLATION'
>               3 Prepare    SELECT * FROM stanice WHERE Sit_ID = ?
> ORDER BY Sit_ID, Podsit_ID, ID
>               3 Execute    SELECT * FROM stanice WHERE Sit_ID = 1
> ORDER BY Sit_ID, Podsit_ID, ID
>               3 Prepare    SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS 
> TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 
> 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 
> 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice'  
> ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
>               3 Execute    SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS 
> TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 
> 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 
> 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice'  
> ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
>               3 Close stmt
>               3 Reset stmt
> 
> MySQL connector:
> 
>               3 Query    SELECT @@tx_isolation
>               ...
>               3 Query    select * from stanice
>               3 Prepare    SELECT * FROM stanice WHERE Sit_ID = ?
> ORDER BY Sit_ID, Podsit_ID, ID
>               3 Execute    SELECT * FROM stanice WHERE Sit_ID = '1'
> ORDER BY Sit_ID, Podsit_ID, ID
>               3 Close stmt
> 
> I think those "unnecessary" queries are making difference in performance 
> of those connectors. Does anybody have any ideas what could I do to 
> speed up MariaDB's connector? We prefer not using MySQL connectors anymore.
> 
> Thanks in advance
> 
> Dušan Pavlica
>