Thanks. You idea of appending into a buffered cursor is certainly faster. The only disadvantage to this technique is that all rows are considered inserts which leads to duplicates, but I can work with that....
Troy
>After re-reading, I think I understand now.
>I tried the following on an AMD 1.2Gig machine w/256 MB ram...
>
>1) used a local table with 11 fields and 900+ records and added a memo field populated with repl('x',500)
>2) appended from self to get almost 500K records
>3) copied stru to test
>4) use test
>5) cursorsetprop('buffering',5)
>6) appended from table w/500K records
>
>It took just about a minute to populate.
>
>Perhaps you may try it this way.
>Also check to ensure that your temp table is local. If it has to go thru the network, this will undoubtedly slow things down. (ie. ?dbf())
>
>If this is the case, you can set the tempfiles to a local dir (c:\windows\temp for example) but using
>TMPFILES=C:\windows\temp
>in your config.fpw file.
>
>HTH
>>How can I fool a cursor into being fully buffered after setting table buffering? The situation we have is that we use cursorsetprop functions with the tableupdate and sqlcommit functions to update the live tables from a buffered cursor. The problem is that the updates to the local cursor are approaching a hundred thousand records and the updates to the local cursor get slower and slower as the buffer gets larger (5 hours). When I turn buffering off, the updates are quick (3 minutes), but then we can't use the following code technique. The batch update function works quickly, it is just populating the local buffered cursor that is killing us. I can turn buffering back on and then scan thru every record and replace each field with itself, but that does not buy us anything. Is there a quick way to fool fox into thinking every record in the local cursor needs to be updated?
>>
>>This is the routine I call with the buffered cursor that updates the back end. This is quick, just dealing with the buffered cursor is slow:
>>
>>
>> FUNCTION Cursor_Save_View(cCursor AS STRING, cTable AS STRING, cPKField AS STRING, tiDataSessionID AS INTEGER)
>> * This routine saves a whole cursor to the back end very quickly by tricking the cursor into behaving
>> * like a remote view. This routine does not perform pre-maintenance on the table: like converting
>> * blank dates into valid SQL Server values or initializing null values.
>> * Primary key values must be assigned before calling this routine.
>> LOCAL loRec, llRetVlu
>> cPKField=IIF(EMPTY(cPKField),'PK',cPKField)
>> tiDataSessionID = IIF(EMPTY(tiDataSessionID),this.DataSessionId,tiDataSessionID)
>> SET DATASESSION TO tiDataSessionID
>> TRY
>> SELECT (cCursor)
>> SCATTER MEMO NAME loRec
>> * Make the cursor updatable to the back end and wrap in a remote transaction
>> =SQLSetProp(THIS.iCH, "Transactions", 2)
>> =CURSORSETPROP("Tables", cTable)
>> =CURSORSETPROP("FetchMemo",.T.)
>> =CURSORSETPROP("KeyFieldList", cPKField)
>> =CURSORSETPROP("UpdatableFieldList", THIS.AllMembers(loRec))
>> =CURSORSETPROP("SendUpdates", .T.)
>> =CURSORSETPROP("UpdateNameList", THIS.AllMembers(loRec,cTable))
>> * Update Locally
>> IF TABLEUPDATE(1,.T.,cCursor)
>> * Commit the changes
>> =SQLCOMMIT(THIS.iCH)
>> llRetVlu = .T.
>> ELSE
>> * Something went wrong, log it and roll back the transaction
>> THIS.ERROR(0,'TableUpdate',0)
>> =SQLROLLBACK(THIS.iCH)
>> ENDIF
>> =SQLSetProp(THIS.iCH, "Transactions", 1)
>> CATCH TO oErr
>> llRetVlu = .F.
>> THIS.LOG("ERROR - "+oErr.MESSAGE)
>> FINALLY
>> IF SQLGETPROP(THIS.iCH,"Transactions")=2
>> * If the Tansaction was not closed, then something went wrong with the update
>> llRetVlu = .F.
>> THIS.LOG("ERROR - Transaction left in flux, therefore performed a rollback")
>> =SQLROLLBACK(THIS.iCH)
>> =SQLSetProp(THIS.iCH, "Transactions", 1)
>> ENDIF
>> * Disconnect the cursor and cleanup
>> =CURSORSETPROP('SendUpdate', .F.)
>> =CURSORSETPROP("Tables")
>> =CURSORSETPROP("KeyFieldList")
>> =CURSORSETPROP("UpdatableFieldList")
>> =CURSORSETPROP("UpdateNameList")
>> ENDTRY
>> SET DATASESSION TO THIS.DATASESSIONID
>> RETURN llRetVlu
>> ENDFUNC
>>
