Hi all,
I am converting a big VFP application to MYSQL and for now using VFP as a frontend. I converted the database (over 100 files) and the data (about 2 gig) and that went smoothly. If anyone is interested how to I will post that here. Overall it is workable and I tried now to improve performance an there is where the problems started.
Here is the basic overview
MYSQL Server 5.1.41 on a windows vista machine
MYODBC 5.1.6
VFP 9.0 sp2 on a toshiba satelite running XP
To access the server I use shared connections as setting up a non shared connection can take 0.5 seconds as compared to a shared one that is < 0.08 sec. This makes a difference if you have to retrieve data from 8 or 9 tables when opening a screen. Using seq. sync mode and nonshared connection the data access woud take 15 seconds a long time waiting for a screen to open. Opening up 10 shared connections simultaneously and running the queries asyncronously the data access part is done in 0.6 seconds (some of the data will fill in later but that is ok as the customer can start scrolling through whatever he wants and sees progress and does not sit in front of the screen waiting for 15 seconds with nothing happening.
Now to improve performance I started to change some settings in the odbc driver.
I set the compression setting on - did not do much on that screen but improved the data availability on another screen (accessing about 100,000 records out o a 2 Million record table with a complex where clause and a join involving 4 other tables using a view. Synchronously it takes 140 seconds (as all needs to be read) async data is available in 30 seconds with compression in 16.
Now I also tried to set "don't cache results on forward only cursors" this made dat available in 6 seconds - but then if the customer does not want to see the whole dataset and cancesls (sqlcancel(connection_id)) the system hangs. Also when running those 10 sql statements from the 1st example as soon as the 2nd simultaneous query executes one gets and error from the odbc driver something like "You cannot execute that command now - commands out of sync SELECT ......"
So there is a problem with that feature. Also forcing all sets to be forward only cursors or eabling/disabling large record sets in the driver does not work.
Also on the 100,000 record query when executing
Select * from <complex views> WHERe <comlex where> ORDER by <field list>
I get erro 11 Cannot unlock table
Droping the ORDER BY will take care of the error - WHY ?
Also a workaround I found when rewriting the above query to
SELECT tmp.* FROM (Select * from <complex views> WHERe <comlex where>) tmp ORDER BY <field list>
Will take care of the error Also if you select less than 40,000 records then the error does not occur at all with the above query. This looks like a MYSQL problem to me as I can reproduce it in the MYSQL query editor.
One more problem :
Using an updatable cursor (by setting the properties with cursorsetproperty()) will fail with an error "Access denied for user ''@'') Turning the debug on on the ODBCDRIVER will show as the last statement
UPDATE <tablename> SET <fieldlist> WHERE <keyfields = keyvalues>
now taking that statement and executing it either in the Mysql Query browser or as code in th program
ln_conn = SQLCONNECT(<server connect values>)
sqlexec(ln_conn, "UPDATE <tablename> SET <fieldlist> WHERE <keyfields = keyvalues>")
Same statement foxpro tries to send.
As a note the user and PW are defined in the DSN so it should use that user and not some weird user ''@''
Any comments or solutions would be apreciated