VFP Updating Remote SQL very slow... 1

[MarkButler]

I have a process which weekly inserts a number of records into a SQL 2000 database. The file is provided to me as a text file with 200K records in it. I create a cursor from the updated text file and then in a scan loop I use a scatter, check to see if the record exists and either do a subsequent gather or insert from memvar into the SQL database. Although the process works it is very slow. I have tried a number of things like setting various buffer modes, etc. The following code is where I am at currently. Any suggestions on getting some speed out of it?

I read where setting the "BATCHUPDATECOUNT" would help but either I am not setting it correctly or it has no effect.

* build the temporary cursors from the text files...
SELECT 0
CREATE CURSOR provlist ( ;
   PVB_FIL_TP C(2),;
   PVB_REC_TP C(1),;
   PVB_PROVID C(11),;
   PVB_TYP_CD C(2),;
   ...
   PVB_UPD_DT C(8))

APPEND FROM (lcPath+'provlist.seq') ;
   TYPE DELIMITED WITH CHARACTER '@'
*
INDEX ON PVB_UPD_DT TAG PVB_UPD_DT
SET ORDER TO
*
WAIT WINDOW 'Obtaining last update date from existing database...' NOWAIT
SELECT provider_max_i
=REQUERY()
llLoad=IIF(EMPTY(provider_max_i.lst_update) OR
ISNULL(provider_max_i.lst_update),.T.,.F.)
lcTemp=DTOC(IIF(llLoad,{^1900/01/01},provider_max_i.lst_update))
lcLstDate=RIGHT(lcTemp,4)+LEFT(lcTemp,2)+SUBSTR(lcTemp,4,2)
*
SELECT provider_m
llLocal=(CURSORGETPROP('SourceType') = 1)
IF NOT llLocal
   * remote view, set up for transactioning...
   lnConnection=CURSORGETPROP('ConnectHandle',ALIAS())
   SQLSETPROP(lnConnection,'Transactions',2)
   SET DATABASE TO CMS_R
   DBSETPROP("provider_m","VIEW","BATCHUPDATECOUNT",100)
*?????????????????????
ENDIF
IF llLoad
   CURSORSETPROP('Buffering',5) && set optimistic table buffering
   BEGIN TRANSACTION
ENDIF
*
llOK=.T.
SELECT provlist
SCAN FOR provlist.pvb_rec_tp='3' AND provlist.pvb_upd_dt > lcLstDate
   IF NOT llOK
      EXIT
   ENDIF
   *
   llInsert=.T.
   IF NOT llLoad
      SELECT provider_m
      FindPVB_PROVID=provlist.pvb_provid
      =REQUERY()
      IF RECCOUNT()<>0
         * we do a scatter to maintain the pk value
         * and the original add date/initials...
         SCATTER MEMVAR
         llInsert=.F.
      ENDIF
   ENDIF
   *
   SELECT provlist
   SCATTER MEMVAR
   *
   m.pvb_pk=SCMSDGK1("PVB_PK",.T.)
   ...
   ...
   m.pvb_upd_dt=ThisForm.FixDate(m.pvb_upd_dt)
   m.pvb_upd_fl=IIF(llLoad,'N','Y')
   IF llLoad OR llInsert
      m.pvb_add_dt=DATETIME()
      m.pvb_add_in='BWC'
   ENDIF
   m.pvb_chg_dt=DATETIME()
   m.pvb_chg_in='BWC'
   *
   SELECT provider_m
   DO CASE
   CASE llLoad
      INSERT INTO provider_m FROM MEMVAR
      IF MOD(lnThermCnt, lnThermInt)=0
         llOK=TABLEUPDATE(.T.,.T.)
      ENDIF
   CASE llInsert
      INSERT INTO provider_m FROM MEMVAR
   OTHERWISE
      GATHER MEMVAR
   ENDCASE
   *
   SELECT provlist
ENDSCAN
*
SELECT provider_m
DO CASE
CASE NOT llOK
   =AERROR(laError)
   =MESSAGEBOX('An error occured:
'+LTRIM(STR(laError[1]))+CHR(13)+laError[2],0+16)
CASE llLoad
   IF TABLEUPDATE(.T.,.F.)
      IF NOT llLocal
         SQLCOMMIT(lnConnection)
      ENDIF
      END TRANSACTION
   ELSE
      IF NOT llLocal
         SQLROLLBACK(lnConnection)
      ENDIF
      ROLLBACK
   ENDIF
ENDCASE
*
RETURN llOK

TIA
--
VFP7 SP1
Mark Butler

 

[jimoo]

One basic tip with SQL is to let the server handle the CRUD.

Create
Read
Update
Delete

Build Stored Procedures to handle the CRUD. Call the stored procedure from VFP using the SQLEXEC() function.

This is far more effienct than passing the sql command.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[MarkButler]

I did do a bulk insert and it took 45 seconds to insert the 200k records into the database. The above procedure was going to run for 5 hours. I cannot use the bulk insert because I have to be able to do some selection, add default values and determine inserts vs updates (never deletes). With that said I can see the value of letting SQL stored procedures do the actual I/O to the SQL database.

My only dilema is I have never created a stored procedure. But it is the weekend and I can invest the time to learn. Can you or someone point me at a sample to get me going? How would I pass the field values to the stored procedure?

TIA

 

[baltman]

The

http://www.vfug.org

newsletters for Jan and Feb 2004 have articles on SQL server and VFP written by By Les Pinter. I believe the Feb one concerned stored procedures.

Brian

 

[MarkButler]

The articles are of great help. Thanx