windows

Thank you for your report.
Could you elaborate a bit what is slower. any query, any operation, connection establishing itself? The best would be to provide ODBC trace for your case.

Query return is slower so as connection establishment too. in MySQL odbc it took 3-4 seconds and in MariaDB odbc it took 8 to 10 seconds delay in each task. I am testing from INDIA and my Remote server is in GERMANY. I have MSSQL too in same location and it's working fast on odbc too.

I would still ask you to provide ODBC trace. It's hard to imagine that could cause such a performance difference on any query return. Where must be something more than sending query and receiving results, that VIsual Foxpro does.
For connections establishing some more details would also help. Could you please provide your connection strings? I don't need host names and passwords or any other sensitive data, of course.

Actually, it's better to provide ODBC trace right from the connections establishing. It's again possible, that Foxpro does something under cover after that. Like reading some metadata or something else.

Ok, I will update the ODBC trace. But the same VFP works fine with MySQL ODBC 8.0

I understand that. Something is slower in MariaDB driver. But we don't know what exactly. It can't be everything. And we don't know how exactly VFP uses ODBC without seeing the trace.

SQL.LOG

I have attached odbc log for MariaDB. if you required log for MySQL odbc i can send that too.

Regards

Thanks for the log. As for mysql log - that may be also useful. It's possible, that depending on info provided by drivers VFP use different approaches for some operations.
Could you also provide me you connection string/dsn info? or at least outline what options do you use in both cases and if they are the same.

for MariaDB Connection string is
ODBC=

;Server=<<SqlServerName>>;Database=<<SqlDataBaseName>>;User=root;Password=<<SqlServerPassword>>;Option=83888160;

for MySQL Connection String is
ODBC=

;Server=<<SqlServerName>>;Database=<<SqlDataBaseName>>;User=root;Password=<<SqlServerPassword>>;Option=83904632;
Attached Mysql Log too.

Regards

MYSQL.LOG

Looks like you are using MariadDB server, but I better ask if that is correct assumption? also, what is the version of that server?

Correct Mariadb Server. Version 10.3

Would it be also correct to say, that execution of the described by traces takes 3-4s with mysql, and 8-10s with mariadb driver?
And could you also share figures that ping shows from your location to the server

Yes with MySQL odbc it took 3-4 seconds and with MariaDB Driver, it took 8-10 seconds.

here is ping figures.

Pinging 79.143.180.23 with 32 bytes of data:
Reply from 79.143.180.23: bytes=32 time=237ms TTL=117
Reply from 79.143.180.23: bytes=32 time=175ms TTL=117
Reply from 79.143.180.23: bytes=32 time=228ms TTL=117
Reply from 79.143.180.23: bytes=32 time=172ms TTL=117

Ping statistics for 79.143.180.23:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 172ms, Maximum = 237ms, Average = 203ms

Here is Trace route too

Tracing route to controllprocesses.contabo.host [79.143.180.23]
over a maximum of 30 hops:

1 1 ms <1 ms <1 ms 192.168.18.1
2 7 ms 11 ms 9 ms 124.253.216.1
3 8 ms 5 ms 5 ms 103.132.144.177
4 7 ms 7 ms 7 ms 103.132.144.35
5 7 ms 6 ms 6 ms 112.196.18.169
6 13 ms 13 ms 14 ms 125.19.65.17
7 180 ms 173 ms 192 ms 116.119.36.142
8 182 ms 175 ms 178 ms linx-ge7-0-oscar.lon.router.colt.net [195.66.224.49]
9 179 ms 181 ms 215 ms 212.74.85.70
10 177 ms 178 ms 191 ms 212.74.85.70
11 172 ms 167 ms 167 ms 212.78.183.246
12 174 ms 177 ms 169 ms controllprocesses.contabo.host [79.143.180.23]

Trace complete.

Could you please try to test the same scenario with turned off compression? You need to deduct 2048 from your OPTIONS value - that bit stands for compression. I wonder if time difference gonna be of the same scale

yes, it improved by 20%. now it took 7.40 seconds. which was early approx 9 to 10 seconds.

The main theory is that is because of prepared statements in maodbc vs text protocol in myodbc.
MariaDB has some optimization for direct execution of the prepared statements. But it's disabled, when compression is used. But it apparently it doesn't remove all the overhead.
However, mariadb log has 388 more API calls. And in particular 9 more calls of SQLDirectExec, i.e. 9 more queries. With all accompanying metadata calls, rows fetching, column data getting etc, they actually can be responsible for all those 388 calls. Anyway, those queries and calls are making ~10% of total number and unlikely can be responsible for the whole performance time difference. But still requires some research at least to figure out why are they there, and not in the myodbc trace.

One more area you need to research when we connect the remote server MariaDB odbc lost the connection within a minute until you have an auto-reconnect option enabled. which is not required in the MySQL odbc case.

That is probably they either use either longer wait timeout by default, or set interactive client option by default, that eventually means the same - longer timeout. I am not sure that is a good idea, but we need to add that option. I opened ODBC-288 for this.

Some of cases described there can affect this issue as well

3.2.0 with text protocol support is about to be released. I am tempted to close this ticket, as I suspected binary vs text was slower in this case, but leaving it open to take another look.