first hunch was along Sergeys reply, guessing that the time is lost in the tableupdate() of the RV. Swag would have been to create a file for bulk uploading, although through hearsay SQL Server linking to vfp data and slurping that in might also be faster than tableupdating a RV.

From your log you loose most of the time calling the fix of 5 dt fields each with a where identifying the row needing an update. From the time spent, no index on theses dt fields. If you are working off a classic HD, creating additional update field clauses and adding them to the first SQL statement should be much faster.

* Concat update iif for all dt fields into string
* evl() if others also on the project know vfp,
* but iif() should not be much slower and the string should be created by a function working off afields or looping field() IAC ;-))

* PK, FK, GISLink columns plus concat of dt fields : 3+4 missing 
UPDATE rv_Activity SET ;
  PKCol = "C" + ALLTRIM( PKCol ) ;
  , FKCol = "C" + ALLTRIM( FKCol ) ;
  , GLCol = ALLTRIM( GLCol );
  , DateTimeColX1 = iif( TTOD( DateTimeColX1 ) = {^1900-01-01}, .NULL., DateTimeColX1 );  
  , DateTimeColX2 = iif( TTOD( DateTimeColX2 ) = {^1900-01-01}, .NULL., DateTimeColX2 );
.... alternatively
  , DateTimeColX5 = evl( DateTimeColX5, .NULL.)

even if you are working on a SSD, if many dt fields are in the table there is a very good chance that skipping reading the whole table (no index...) x times is faster than writing identical values once into the dt fields even if writing speed in relation to reading speed on SSD is much "worse" than the same relation of those operations on classic HD.

If you test, please share the log with some info on disc used -even if we both do NOT like to mistreat SSDs with such coding;-)))

If you are after utmost speed, my hunch is that creating for each table a special SQL select cmd including the updates of step one for the necessary fields plus the evl() for the date fields with a "to" clause for bulk import will be fastest, but the speed gain over my proposed solution is not great enough to warrant the loss of interactivity/debug ease.
HTH

thomas

>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.