>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