Bianca,

>So, every copy of the program that runs looks for the data files on drive M in the \POS\DATA directory. The VPN box in the remote locations (yep, hardware VPN, not software) handles the translation of the letter M into the server in Burbank via the IP address for that server. Sounds a bit complicated but is really simple.

>The problem is the time it takes to translate the data request, send that request to the server, re-translate to a "local" SQL request, receive the answer, translate that to the requesting network/node address, transmit that back to the sender, re-translate it again into a "local" answer dataset and present it to the calling application. It is all this translation and transmission time that is messing up VFP to begin with.

I understand that these words are from your network people and maybe don't reflect the reality of what is happening. First, they talk about mapping the letter M to the server in Burbank when the remote program "looks for the data files".

But in the next paragraph they talk about the time to "re-translate to a local SQL request, receive the answers, translate that... etc". The first paragraph sounds like you have just mapped a drive letter to the server's VFP data files across a VPN and the client program USEs or SELECTs from those tables.

If so, you SHOULD be getting very slow response, especially if the tables are very large because there are no SQL commands being executed on the server -- just on the remote client computer which tries to read the DBFs (index nodes, matching records, or even entire tables) across the VPN into memory on the client computer.

However, the later paragraph sounds like maybe you actually have some kind of VFP server program running on the server that receives query requests, gets the data, and somehow gets it back across the wire to the client, where it gets turned into a VFP cursor (perhaps using XML as the data transport format) -- thus these words: re-translate it again into a "local" answer dataset. Somehow I suspect that the first case is correct and that you don't really have a server process, but are directly reading the data files across the VPN.

>When we add the extra layer of translating the VFP SQL call into MySQL call and re-translating the MySQL answer into a VFP answer (the essence of an ODBC driver) I fear we will be adding more time to the whole process slowing things down even more. While we probably won't be corrupting the My SQL database due to the communication delay (like we are the VFP databases) we're not going to do the client much good by delaying an already slow response.

First, "the essense of an ODBC driver" is not translating a VFP SQL call into a MySQL call and re-translating the MySQL answer into a VFp answer. When using ODBC against MySQL, you send through ODBC a SELECT statement (or Insert, Update, Delete) that is already formatted for MySQL's syntax -- either via SQL Passthrough, or by letting a Remote View or CursorAdapter create the statement for you dynamically. The answer comes back via the ODBC driver into a VFP cursor automatically, with no additional "external" translation needed.

Please give some more details, because if you really do have a server process that executes queries and returns results back to you, you might not get much improvement to "add the extra layer of translating" between VFP process on the server and MySQL or substitute MySQL as the database -- unless you cut out the translations (to/from XML for instance) and let the client programs communicate directly with MySQL across the wire via ODBC.

Tell us more about what happens on the server. Is there a VFP process running there (perhaps Web Connection or a remote COM server called via DCOM) that is called from the client program, or do the client programs just USE the tables or SELECT directly from the tables across the wire?