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.