MySQL TABLEUPDATE from VFP Cursor 2

[David Higgs]

I am trying to update a MySQL Table from changes made in a VFP Cursor.

The following code updates the field "Call_160" in Cursor "curLarge_Squares" with "Test".

SQLEXEC(SQLCONN,'SELECT * FROM &TBL_WAB_Large_sq ','curLarge_Squares')
INDEX on LG_SQUARE TAG lg_square

CURSORSETPROP("AutoIncError", .F. , "curLarge_Squares")

SET MULTILOCKS ON

=CURSORSETPROP("Buffering", 5, "curLarge_Squares")

GOTO TOP 

Replace Call_160 with "TEST" 

BROWSE 

=TABLEUPDATE()

I would now like to update the MySQL Database to match the VFP Cursor but I am not too sure how to proceed.

Any help would be much appreciated.


Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

UPDATEs default "scope" is all records, unless you write a WHERE clause limiting the record you want to update. Therefore the UPDATE command I gave is updating the whole cursor. And REPLACE has the ALL scope you can add when you want to update more than the current record.

Your loop has one advantage, though, it goes through the cursor in sort order even when you SET INDEX or SET ORDER to an index TAG. RECNO() then jumps around in its values, as it remains the physical record number that's not the row number in sort order, generally.

I wonder what's going on, because even a loop like your does only need a second for a million records for me, when SET NOTIFY CURSOR is OFF.

SET NOTIFY CURSOR OFF

CREATE CURSOR curTest (Sortorder int)
APPEND BLANK

FOR A = 1 TO 20
   APPEND FROM DBF('curTest')
ENDFOR 

* try it with NOTIFY on
* SET NOTIFY CURSOR ON

StartTime = SECONDS()
? StartTime
A = 0
SCAN
   A = A + 1
   replace Sortorder WITH A
ENDSCAN

EndTime = SECONDS()
? EndTime
? EndTime - StartTime

I actually still think this is the setting that causes the long duration, because if the notifications are on VFP write "0 recordss replaces" followed by "1 record replaced" for each REPLACE. And that cuases a million times to create the GDI+ graphics for this text with the true type font that involves drawing bezier curves. You're just wasting the time to update the status bar text faster than you can read with info nobody needs to see. Maybe you put SET NOTIFY CURSOR in your main.prg but it's likely one of the settings that goes back to default with each new datasession.

Chriss

 

[David Higgs]

Using the following code..........

*                        
*  Alternate Row Colours 
*  --------------------- 

PROCEDURE Alt_Row_Colours

LPARAMETERS alt_col_selection	

SELECT (alt_col_selection)		

ninc = 1
StartTime = SECONDS()
SCAN 
	ninc = ninc + 1
	Replace alt_col With ninc
ENDSCAN
EndTime = SECONDS()
MESSAGEBOX(EndTime - StartTime)
ENDPROC

with the CURSORSETPROP code it takes 10.944 Secs to execute 5660 Records. Without the CURSORSETPROP code it takes 0.172 Secs.

SET NOTIFY CURSOR ON or OFF makes no difference.


Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

with the CURSORSETPROP code it takes 10.944 Secs to execute 5660 Records. Without the CURSORSETPROP code it takes 0.172 Secs.

If the CURSORSETPROPS are operating on the alt_col_selection cursor, then the above statement doesn't surprise me. That's because, each time round the SCAN loop, you are actually sending an update to the MySQL table. Given that is far slower than anything you are doing in VFP, it is certain to take a lot longer.

Given that you want to add a consecutive number to each record in the cursor, an easy way of doing that would be:

[tt]REPLACE ALL alt_col WITH RECNO()[/tt]

(assuming that there are no deleted records and no index order in place).

That is just one update rather than one per record, so should be much faster.

An even better approach might be to write a stored procedure on MySQL to insert the numbers there. But that might be a level of complication that you would prefer to avoid.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Given that you want to add a consecutive number to each record in the cursor, an easy way of doing that would be:

REPLACE ALL alt_col WITH RECNO()

(assuming that there are no deleted records and no index order in place).

That's the problem Mike, I use "SET Order to" and "Filter" (none were active at the time of my tests) so RECNO() wouldn't work.

I think I may have to re-consider the benefits or otherwise in using CURSORSETPROP. It may not be suitable for my Logbook but my be beneficial in other Databases that are part of the application.

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

No, Mike,

when buffering is set to table buffering, no updates go to MySQL until you execute TABLEUPDATE(), only row buffering modes will send the current row in the event of a SKIP, so if it's NOTIFY it should be that, but that doesn't go well with CURSORSETPROP("Buffering", 5, "curLogbook"), as David posted.

I'd go deeper with SET COVERAGE:

*                        
*  Alternate Row Colours 
*  --------------------- 

PROCEDURE Alt_Row_Colours

LPARAMETERS alt_col_selection	

SELECT (alt_col_selection)		

ninc = 0
StartTime = SECONDS()
? 'Buffering set to :',CURSORGETPROP('Buffering','alt_col_selection')
? 'Notify Cursor:',SET('NOTIFY',1)
? 'Talk:',SET('Talk')

SET COVERAGE TO scanloop.log
SCAN 
	ninc = ninc + 1
	Replace alt_col With ninc
ENDSCAN
SET COVERAGE TO
EndTime = SECONDS()
MESSAGEBOX(EndTime - StartTime)
ENDPROC

And you can also do SQL logging in the MySQL driver.

https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-trace-log.html

I don't know which driver you use, details can vary. I would expect no log while you don't TABLEUPDATE(), so that's just to double check if Mike still is right about the updates per record going to MySQL. I doubt that, unless the buffering you set to 5 later is changed to something else. It matters what's the current state when you go into the loop.

The coverage log should have about 4 times as many lines as the logbook has records, as a scan loop has 2 code lines plus the SCAN ENDSCAN logged, that's the first thing to check and then whether the SKIP or REPLACE line (you need to reidentify which log line is about which code line purely by the line number) takes unusually long, every time or even more interestingly only sometimes.

Chriss

 

[Mike Lewis]

Just to double check if Mike still is right about the updates per record going to MySQL. I doubt that, unless you set buffering to 5 somewhere and later it's something else.

No. With any table buffering, Chris is right. The updates will be sent when you do the TABLEUPDATE(). I would think that would be much faster than sending them individually. But with row buffering, moving the record pointer will cause the updates to be sent per record. I'm guessing that's what is causing the huge slowdown that you are seeing, David.

Regarding SET NOTIFY CURSOR, in general, sending any output to the screen during a long process will slow it down. So any kind of progress reporting, such as a so-called thermometer, will slow down the very progress you want to report. It's always a balance as to how much information to give to the user, or how often to give it. In the case of SET NOTIFY CURSOR, I would guess any slowdown would be pretty negligible, as it only occasionally writes to the screen, and only a small amount of data is written each time.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

? 'Buffering set to :',CURSORGETPROP('Buffering','alt_col_selection')

Gives an error No. 52 "No Table Open in the current work area"

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

Hi David!

How's that?
You just SELECT (alt_col_selection).

Ah, stupid me, that means alt_col_selection isn't the alias, it contains the alias name, so
? 'Buffering set to :',CURSORGETPROP('Buffering',alt_col_selection)

or even simpler, as it is the current workarea:
? 'Buffering set to :',CURSORGETPROP('Buffering')

Chriss

 

[David Higgs]

? 'Buffering set to :',CURSORGETPROP('Buffering')

Buffering set to: 5
Notify Cursor: ON
Talk: ON

Time to execute 11.306 Secs

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

And with SET TALK OFF, SET NOTIFY CURSOR OFF?

Besides, now I'd be interested in the MySQL ODBC log and the coverage log.

Chriss

 

[David Higgs]

And with SET TALK OFF, SET NOTIFY CURSOR OFF?

Buffering set to: 5
Notify Cursor: OFF
Talk: OFF
Time to execute 11.306 Secs

I'd better just mention that the first time I run PROCEDURE Alt_Row_Colours the execution time is 0.547 Secs.

Besides, now I'd be interested in the MySQL ODBC log and the coverage log.

That is probably going to be way out side my comfort zone!




Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

Well, there's not much code to analyze is there. There might be a surprise coming from the coverage log file in that the replace might cause an update trigger code to run, though I wouldn't know how a cursor created by SQLEXEC would have a reference to some update trigger.

I think I can only ask one more question not out of your comfort zone, that is how many records are in the logbook cursor?

From my code example about the NOTIFY effect I can estimate a million records can be updated with single REPLACEs in a second and that's blastingly fast. So what is the record number? 10 million would justify 10 seconds update time.

Chriss

 

[David Higgs]

I think I can only ask one more question not out of your comfort zone, that is how many records are in the logbook cursor?

There were 5663 Records in the Logbook when I carried out the tests.

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

So this means you only update about 500 records per second?

Where is your TEMP folder? And how much RAM do you allow VFP?

Usually the TEMP folder shouldn't even play a role as cursors are held in memory. But REPLACES only can get that slow when the cursor needs to be paged to HDD and when that is not a local drive. Or when you have update triggers that take long.

How much RAM do you have? More than 4 GB? Then give VFP all it can take, 2GB, there still is enough for anything else.

And last not least, you say without all the CURSORSETPROP it only takes split seconds. So what actually changes from first to further times? I don't know how that could happen. The usual behavior about performance is taking a long first run and then profits from caching and gets fast in secondary passes.

I saw you did SET MULTILOCKS ON, which is important for table buffering to work. So nothing obvious.

It would be interesting to see the coverage log, if it really does not show up trigger code and the scan loop is all that runs, it must runm through a small bottleneck by wrong TEMP configuration and low memory available to VFP.




Chriss

 

[David Higgs]

Temp Folder C:\USERS\APPDATA\LOCAL

SYS3050 (1) 1.45GB

SYS3050 (2) 366MB

RAM 8GB

For my tests I ran one program with the CURSORSETPROP Code and the other without the CURSORSETPROP Code. Before I started attaching the CURSORSETPROP code I made a copy of my original code and then modified the copy, so I was able to use one with and one without for the tests.

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

Okay, good idea. now I fear you can't solve this without leaving your comfort zone and go deeper into what actually happens during a single REPLACE that takes so long.

Chriss

 

[David Higgs]

I fear you can't solve this without leaving your comfort zone and go deeper into what actually happens during a single REPLACE that takes so long.

I will have a chat with my son and see if he can help with the MySQL Logging.

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

I remember one thing someone experienced with the wrong settings of views, if you set the connection to not fetch data unless you navigate to it, you get a single record at first and then single rows are fetched when you SKIP, which only seems a very good idea when you check how fast SQLEXEC finishes, but you just defer the job to load the data and when you only fetch it record by record that takes much longer than getting the full result.

It can also matter what field types you use, but I can say more about MSSQL than MySQL. Memo fetching can take longer.

One thing to try is another driver. Use an ANSI driver for VFP, not a Unicode driver.

Chriss

 

[Chris Miller]

You just need to start up ODBC Data Sources (32 bit) and then there should be a tab about tracing.

Chriss

 

[David Higgs]

Chris,

With the help of my son, we ran the "ODBC Data Source Administrator (32-bit)" and we turned on tracing. There was activity when I first loaded my Database from MySQL into the curLogbook but there was no activity whilst the SCAN/ENDSCAN was taking place. The Alt_Col should only be updating the curLogbook anyway and not the MySQL Database.

Probably not a lot of help but that's about the best we can do.

Regards,

David.

Recreational Developer / End User of VFP.