about @@tx_isolation, mysql deprecate it to replace it with @@transaction_isolation so query has to take server type and version in account

I added general query logs and odbc trace logs for both mariadb and mysql connectors.

DusanP May I ask you to try to use "Force use of forward only cursors"(FORWARDONLY=1 or MADB_OPT_FLAG_FORWARD_CURSOR bit of OPTIONS). I am interested if that will remove those seemingly redundant SQLPrimaryKeys calls(queries from INFORMATION_SCHEMA.COLUMNS in the query log). The purpose of calling them is not clear - I can't see in the trace how SQLPrimaryKeys' results is used. And what can make Embarcadero want to call them is also not clear. To force forward only cursor is my best idea so far. As we have bug of having static, and not forward only cursor a default cursor type(that will be changed in the 3.2 version), and forcing forward only should change that default.

I added new mariadb logs created in same way as older ones now with 'Force use of forward only cursors' flag checked.

DusanP I am not quite familiar with Embarcadero, but atm installing it looks like the best option to figure out why does it adds those SQLPrimaryKeys calls comparing to MySQL's driver. Would it be possible to provide from your side some basic project that would expose those calls?

I created simple project in C++ Builder 10.4. It contains one connection component, several query components for inserting, deleting and updating records and components for displaying records. There is also create table script and already compiled runnable application in 7z file.

Sorry, the fix for the main problem here hasn't made for our last release(3.1.13), simply because I could not yet identify the cause. Other performance optimizations I moved to the separate ticket, and they are in the 3.1.13. I think they should be noticeable in your application if you upgrade the driver to this version. But, as I said, the main problem hasn't been resolved yet.

Hi, I did some tests with 3.1.17 version just to check if there are some improvements or progress and was surprised that 3.1.17 is much worse then even 3.1.12. I tested loading of our program and with 3.1.12 it took about 30s to start, with 3.1.13 17s and with 3.1.17 it lasted 120s. MySQL ODBC driver 8.0.24 was the quickest - about 5s. All tests were performed with MariaDB server 10.5.9.
Is there any chance this issue will be resolved?

Thank you for the new information. Looks like I also have to look into this drastic performance degradation. Is it happening on the same database? SQLPrimaryKeys requests INFORMATION_SCHEMA, and queries to INFORMATION_SCHEMA tend to take more time when the there more shemas/tables/columns.
I've given higher priority to the ticket, and I'll give another try once I have time.
Actually, I could use other approach here - and optimize SQLPrimaryKey making it optionally not to use INFORMATION_SCHEMA, and be much faster. The quality of the data may be slightly worse, i.e. some fields may not contain data, but most important info will be present

We use one database with more then 100 tables. And all tests were done on one db server with same data. I switched only connectors, I repeated every test several times and before each test I restarted db server so every test had same conditions at the begining.
And as for the INFORMATION_SCHEME requests, I donť know much about those processes and calls under the hood, I'm just working with usual SQL queries and thus cannot comment which approach could be better. I just need to send queries and get them processed as quickly as possible.

This is quite a performance regression. Could you also provide a trace for the execution with 3.1.17 driver. I will need to compare with your earlier traces. 120s vs 17s - there must be some reason for that. I guess even if I wanted to deliberately make the driver slower, such regression would be hard to achieve.

I created trace logs for MariaDB 3.1.13, 3.1.17 and also for MySQL 8.0.24 ODBC connectors. I added also general logs from db server for all connectors. ODBC logs.7z

DusanP I've found the reason for SQLPrimaryKeys calls and eliminated it. But with the performance regression in 3.1.17 it's not clear.
Basically, 3.1.17 introduces resultset streaming, aka not caching the whole result on client, but fetching it row by row. It would be a good explanation of performance regression. Can you verify if you have NO_CACHE/STREAMRS or 1048576(20th) bit in OPTION set? Probably the bit, as it was always there, but was not really supported before.
Also, you say that your application loads 120s with 3.1.17 and 5s with MySQL's driver, but looking at respective logs, I see that both drivers are on par till "select * from stanice" - ~5s, and then both drivers are seemingly fetching its results ~1.5min since next query comes aprx 1.5min later(actually 3.1.17 finishes like a dozen seconds faster). So, it's not clear for me when is the moment of "application has loaded" from the standpoint of logs. 120s for 3.1.17 falls aprx on end of "select * from stanice" data fetching, and 5s for MySQL driver - on that query execution moment. Btw, embarcadero is very inefficient in that fetching, but unlikely you have any influence on that.
I'm gonna attach here driver library file - it would be interesting if you could try it to see how the situation has changed.

I am closing it so far, as the problem described in this ticket I believe is solved. As for performance regression - I need feedback, and in case of need we can open new ticket.

Lawrin I checked the bit in OPTION set and it wasn't set (I had OPTION=0). I also tried to set OPTION to 1048576 and there was no difference.
But I have good news. When I replaced original 3.1.17 maodbc.dll with the one you sent me, then time of loading was almost same as with MySQL driver 6s your driver vs. 5s MySQL driver. I must admit I don't use very sophisticated measurement metod - I just always measured time from launching program to the moment GUI showed up after all necessary data was read out of database).
As for the differences you couldn't see - they were visible in logs from database server, not in ODBC trace logs. With tracing everything lasted many times longer. I couldn't see any timestamps in trace logs. Every test followed the same scenario - set up configuration, delete log, start db server, launch program, stop program when GUI showed up, save log. But it doesn't matter anymore, I hope.
You've done a great job .

Thanks for feedback. Still not clear why 3.1.17 so slower for you application, than 3.1.13, but I am inclined to blame Embarcadero Given that the thing, that made our driver slower for Embarcadero is beyond ODBC specification, I guess I have the right for such assumption. Main reason for bad performance is eliminated anyway