remote view TABLEUPDATE() of 1,000 records

[opticalman]

I am using VFP 8 with MS SQL EXPRESS as a remote backend

I have been troubleshooting a slow TABLEUPDATE(). It is a table of charges, payments, balance, and date. I have reduced the time of the TABLEUPDATE() from 110 seconds to less than 1 second. Here are 4 pseudo-code examples of what I did.

** example one, original code
** new record has been added via grid control and tablebuffering
TABLEUPDATE( .t. , .t. ) && takes .01 seconds
** code modifies 1000 records
TABLEUPDATE( .t. , .t. ) && takes 115.0 seconds
** new record is removed from grid
** must refresh to see new record

** example two, stop updates does not help
** new record has been added via grid control and tablebuffering
TABLEUPDATE( .t. , .t. ) && takes 0.017 seconds
CURSORSETPROP("SendUpdates", .F. )
** code modifies 1000 records
CURSORSETPROP("SendUpdates", .T. )
TABLEUPDATE( .t. , .t. ) && takes 117.0 seconds
** new record is removed from grid
** must refresh to see new record


** example three, success
** new record has been added via grid control and tablebuffering
TABLEUPDATE( .t. , .t. ) && takes 0.017 seconds
CURSORSETPROP("SendUpdates", .F. )
** code modifies 1000 records
TABLEUPDATE( .t. , .t. ) && ?? commits local table ??
CURSORSETPROP("SendUpdates", .T. )
TABLEUPDATE( .t. , .t. ) && takes 0.17 seconds
** new record is visible on grid
** but is last record, even if date is earlier than previous record

** example four, now works fast,
** data is in correct order if "pre-dated"
** new record has been added via grid control and tablebuffering
TABLEUPDATE( .t. , .t. ) && takes 0.017 seconds
REQUERY()
CURSORSETPROP("SendUpdates", .F. )
** code modifies 1000 records
TABLEUPDATE( .t. , .t. )
CURSORSETPROP("SendUpdates", .T. )
TABLEUPDATE( .t. , .t. ) && takes 0.17 seconds
** new record is visible on grid and in correct order


I have not seen this 'work around' addressed in any article. It is such a dramatic change that I think it is worthy of discussion. My concern is that I have been doing something wrong in the past that slows down the original code in example one. I originally thought this was a record-buffering vs table-buffering issue, but my tests said no. I was also surprised that I needed the TABLEUPDATE() before resetting SENDUPDATES to TRUE. I would appreciate some comments, insights, and/or warnings to what I'm doing.

Jim Rumbaugh

 

[Imaginecorp]

We update tens and thousands of records, but in SQL Server 2005. Updates are instantaneous (second or so). Yes it is always faster, even with native tables, if the table is presorted. It makes me wonder if your problem was "presorted" data in example 4.
This is how we do it.
Create a remote view in designer with send SQL updates enabled
In form, set the view buffering to 5 and in some cases No Data on Load set to .t. depending if child table, (once needed pass it a parameter and do a requery())
Add, delete, and edit records via a grid.
Once done, When “Save” button is clicked all tables are updated with a Tableupdate(1,.t.,<<table/remoteviewname>>) onetime
I am not getting why you are resetting the cursor properties in code?

 

[opticalman]

Imagincorp

Thanks for you input.

I have found problems with the psuedo code I posted. The reason I was resetting the cursor properties, was that I was suspicous that updates were being sent during the view's record updates. Like the the difference between record buffering vs table buffering.

I have found that the code is quick, but none of the 1000 record changes were saved. When I pre-dated a record entry, the table looked good, but after closing and opening the grid, the values had went back to their, values before the local update.

The best solution I have found so far, is to reset CURSORSETPROP( "BATCHUPDATECOUNT", nNumber ). the aprox times for nNumber has been
1 = 130 seconds
5 = 41 seconds
25 = 18 seconds
50 = 13 seconds
100 = 14 seconds
500 = 130 seconds

I believe the best answer would be to put a stored procedure on the SQL server, but I have never done that before. I know I would have to find a way to store the BALANCE to a temp variable between record updates. I may just have to dig and learn. Or hack some more at VFP.

I do have a question. I understand that a SQLEXEC UPDATE command is fast, but when you say you update tens and thousands of records in a second or so, are you modifing local views then TABLEUPDATE, or are you sending a SQL UPDATE command to the backend??

Jim Rumbaugh

 

[opticalman]

I now have an answer that works 'good enough'. The times I posted above, were over a WAN, from my home, via internet, to my office 10 miles away. When I run the code at my office on a LAN, the time for 1300+ recods is about 2 seconds or less. This is acceptable in my application.

Here is what I've learned about TABLEUPDATE and remote data.

1) the more records that need to be updated, the longer it takes.
2)Setting the views BATCHUPDATECOUNT number can greatly increase performance. ( It even says so in the HELP file )
3)LAN is faster than WAN
4) my first attempt failed because calling TABLEUPDATE with SENDUPDATES=.F. told the buffer that everything was saved. When I reset SENDUPDATES=.t. and called TABLEUPDATE, the buffer said, 'nothing new to send'

I consider this problem solved

Jim Rumbaugh

 

[Imaginecorp]

Sorry just came on;

All our updates and inserts are done with predesigned remote views, which may not apply to what you are doing.

I am glad you solved the problem.