ODBC Connection to MySql is considerably slow

[Rajesh Karunakaran]

Hi friends,

I am connecting and read/write to MySql using ODBC 8.0. While things are working, I find the communication between VFP vs MySql seems to be slower than what I would expect. By the way, the MySql is in cloud, may be that affects!

Connection:

lcConnStr = "Driver={MySQL ODBC 8.0 ANSI Driver}" + ;
				";Port=" + lcPort + ;
				";Server=" + lcSrvr + ;
				";Database=" + lcDb + ;
				";Uid=" + lcUser + ;
				";Pwd=" + lcPwd + ";"

Commands like in:

lCmd =	"UPDATE table SET " + ;
			"type_id = " + ALLTRIM(STR(m.type_id)) + " " + ; 
			",datetime_from = '" + m.datetime_from) + "' " + ;
			",updated_id = " + ALLTRIM(STR(gUserId)) + " " + ;
			"WHERE id = " + ALLTRIM(STR(m.recid))

If anyone has any thoughts on this, please share.

Thanks
Rajesh

EDIT: I think the above query is too generalized. Let me check, do some trials and come back here with more precise observations & figures.

 

[Nigel Gomm]

Rajesh,

if you run the same query from Heidi (or workbench) is is significantly quicker than ODBC?

Is there an index on table.id ?

you might add ";options = 2048+8" to your connection string to set "big packets" and "compression" which probably wouldn't help this query but may help others.


hth

n

 

[Steve Meyerson]

the MySql is in cloud, may be that affects!

Rajesh,

I don't know if it's your ODBC version that's the problem.

I use version 3 to access my MYSQL database in the "cloud" (Hostgator). Last night I tested an INSERT with 30 field names and 30 values. It seemed to insert the record instantly.

I noticed your command uses only double quotes. You might need to use some single quotes (or brackets) to isolate the quotes which the database actually sees.

I also noticed you might be missing a quote at the very end of the command.

Hope some of this helps.

Steve

 

[Rajesh Karunakaran]

Hi Nigel,

Heidi/Workbench, I have not tried yet!
The problem was not related that particular query alone. The one I mentioned was only an example.
It works good, but a bit slower. Even the connection take a bit, say 4-5 seconds sometimes.
Isn't that comparatively slower that an ideal condition?

Steve,
Maybe the version! I am not sure. I will have to check.
The quotes the database sees is a single quote in above query creation string. For example, I use as in

str = "datetime_from = '" + m.datetime_from) + "' "
*/ where m.datetime_from could be like 20210-09-10.

The query end, the m.recid is a numeric value. So, I don't need any quote to enclose it.

My queries work good. MySql is getting updated. But, I think it takes much more time than what we would expect. That's the only problem.

Rajesh

 

[Nigel Gomm]

Rajesh,

i too use cloud hosted servers (mariaDB on Azure the other side of the atlantic in my case) and things are only a little slower than a locally hosted server.

Are you reconnecting (sqlstringconnect()) for each query? You would be better establishing a connection once and keeping it for all subsequent queries.

i also had some issue with v8 of the odbc driver and stayed with 5.3.10 (but don't now remember what the problem was - think it was to do with column types).


n