>>>>I have an app that uploads VFP data to SQL Server. Everything works but I'm seeing slow performance. A large part of the slowness is running VFP UPDATE commands against a remote view to a SQL Server database.
>>>>
>>>>A total of 72 tables get imported. Here are the specs for 1 of them, "Activity":
>>>>
>>>>- 55,000 rows, 44MB DBF file size
>>>>- One PK column and one FK column (both C(5))
>>>>- One GIS link column (C(30))
>>>>- Five date columns
>>>>- A bunch of other columns that don't need UPDATE processing
>>>>
>>>>Each SQL Server destination table is initially empty. The general import process for each table is:
>>>>
>>>>1. Create remote view into destination SQL Server table
>>>>2. APPEND from the source VFP table to the RV
>>>>3. Run various VFP UPDATE commands on the RV
>>>>4. TABLEUPDATE() the RV to commit all the changes
>>>>
>>>>The UPDATE commands that are being run against the RV are:
>>>>

>>>>* PK, FK, GISLink columns (call this "Update 1"):
>>>>UPDATE rv_Activity SET ;
>>>>  PKCol = "C" + ALLTRIM( PKCol ) ;
>>>>  , FKCol = "C" + ALLTRIM( FKCol ) ;
>>>>  , GLCol = ALLTRIM( GLCol )
>>>>
>>>>* Unlike VFP, SQL Server doesn't accept blank dates in datetime/datetime2 columns
>>>>* You have to have either a valid datetime, or NULL if the column is nullable
>>>>* We need to have blank dates represented as NULL
>>>>* In the initial APPEND step, SQL Server assigns blank dates a default value of {^1900-01-01 00:00:00.0000000}
>>>>* So, to change each "default" value to the (allowed) NULL value, I'm running this command for each date column:
>>>>UPDATE rv_Activity SET ;
>>>>  DateTimeColX = .NULL. WHERE TTOD( DateTimeColX ) = {^1900-01-01}
>>>>
>>>>* Call the 5 UPDATE commands above "Update 2"
>>>>

>>>>Here are the log results:
>>>>

>>>>[Mar-10 13:04:07] VFP Data Import: processing started for source table [ACTIVITY]
>>>>[Mar-10 13:04:07] VFP Data Import: created and opened Remote View into SQL table [dbo.ACTIVITY]
>>>>[Mar-10 13:04:08] VFP Data Import: APPENDed VFP data into remote view for SQL table [dbo.ACTIVITY]
>>>>[Mar-10 13:04:08] VFP Data Import: Data Update Step 1 started
>>>>[Mar-10 13:05:38] VFP Data Import: Data Update Step 1 completed successfully
>>>>[Mar-10 13:05:38] VFP Data Import: Data Update Step 2 started
>>>>[Mar-10 13:13:07] VFP Data Import: Data Update Step 2 ended successfully
>>>>[Mar-10 13:13:07] VFP Data Import: successfully updated remote view for SQL table [dbo.ACTIVITY]
>>>>[Mar-10 13:14:32] VFP Data Import: successfully TABLEUPDATE()ed remote view for SQL table [dbo.ACTIVITY]
>>>>[Mar-10 13:14:32] VFP Data Import: processing ended for source table [ACTIVITY]
>>>>

>>>>So:
>>>>
>>>>- the APPEND step takes 1 second
>>>>- the single UPDATE command in Update Step 1 takes 90 seconds
>>>>- the 5 UPDATE commands in Update Step 2 take 7min 29seconds (449 seconds, or just about exactly 5* 90 seconds)
>>>>- the TABLEUPDATE() step takes 85 seconds (not sure anything can be done about that)
>>>>
>>>>So, each VFP UPDATE command against the RV is taking about 90 seconds to process, for that particular table. That seems to be very slow.
>>>>
>>>>Other information:
>>>>- computer is a VM with 2 vcores and 8GB RAM. Both CPUs are pinned at 100% during the UPDATE commands
>>>>- the VM is running both the conversion process and the destination SQL Server 2012 Express instance
>>>>
>>>>- the RV has these properties explicitly set, all the others are the default:
>>>>=CURSORSETPROP( "SendUpdates", .T., "_SQLTemp" )
>>>>=CURSORSETPROP( "Buffering", 5, "_SQLTemp" )
>>>>
>>>>- SET MULTILOCKS is ON
>>>>
>>>>- SQL Server database recovery mode is SIMPLE (used to be FULL but log files grew to 19GB and I don't need the advantages of FULL for this)
>>>>
>>>>- No key fields/identities or any kind of constraints on the destination SQL Server tables. They're just empty tables waiting to be filled
>>>>
>>>>What I'm hoping for is that someone can suggest a CURSORSETPROP() setting or similar that I may have missed (or don't know about :)) If push comes to shove I'll create a temporary VFP cursor and do the UPDATEing in that prior to pushing it into SQL Server so I don't think creating SQL Server sprocs will be any easier.
>>>
>>>BATCHUPDATECOUNT
>>
>>I ended up doing updates against a local cursor prior to uploading to the RV so the RV/connection properties are moot in my case.
>>
>>I saw that setting when I was researching this issue. I admit I didn't test it because for each table processed there could be a variable number of UPDATE statements run and it would be a pain to preprocess to figure that out in order to set that value correctly before the real processing.
>>
>>It seems a bit mysterious but from what I could gather it basically concatenates update statements to the number specified, then submits them as a single batch to the backend (?) I'm not sure how that would affect performance in my case.
>>
>>Have you found that setting useful for this type of scenario?
>
>MSQL server (and some others) allow batches of statements to be sent to the server as a single packet, so it speeds updates, deletes, and inserts because the sheer number of network packets sent over the wire is reduced, and the statements can be compiled by the server database as one batch.. You can play around with the PacketSize property too to really tune it. Setting the view to Asynchronous = .F. can help a lot too (unless you want to cancel updates while they're running on the sql server). And lastly - believe it or not, addingTimeStamp can increase performance as well in a lot of cases. Between the BatchUpdatecount and Asynchronous settings you can see a HUGE HUGE differences and these have served me well over the years.

Hmm, interesting.

BATCHUPDATECOUNT: what sort of settings would you use? If I did the following operations:

- APPEND
- then 6 UPDATEs
- then TABLEUPDATE()

what value would you use for it? 7, 8, or ?? How is the batch triggered to be sent - once the BATCHUPDATECOUNT value is reached for pending operations? Or does TABLEUPDATE() trigger it for any pending operations?

PacketSize: looks like that's for maximizing throughput on a network. Not applicable in my case, VFP and SQL Server are on the same host.

Asynchronous: the real value of that is responsiveness of the UI, it doesn't have to wait for the backend to complete. If you're willing to handle the complexities you can build UIs that are much more responsive.

VFP help says "key and timestamp" gives best performance for view updates https://msdn.microsoft.com/en-us/library/aa975641%28v=vs.71%29.aspx so it's believable that would speed up some operations.