Update - SQL enhancement in VFP9

I have a small batch processing module that do something similar as cascade update. Any changes to master table's primary key will also change the foreign key value of child table.

Prio to VFP9, your code may do this by :

1. Call stored procedure from update trigger


2. Add new column to store original PK value, update PK value and then poop thru master table records and update child table FK field accordingly

In my testing, both ways work extremely slow. Thank to FoxTeam on enhancement of Update - SQL in VFP. Now, I can use SINGLE Update - SQL statement to solve my problems above and the performance is much much better.

Here is the testing setup code :

CREATE CURSOR tmpMaster (PK C(10))



FOR I = 1 TO 100

  INSERT INTO tmpMaster VALUES(SYS(2015))

NEXT



CREATE CURSOR tmpChild (PK C(10), FK C(10))



SELECT tmpMaster

SCAN

  FOR I = 1 TO 100

    INSERT INTO tmpChild VALUES (SYS(2015), tmpMaster.PK)

  NEXT

ENDSCAN



ALTER TABLE tmpMaster ADD COLUMN OriginalPK C(10)



REPLACE OriginalPK WITH PK ;

	PK WITH SYS(2015) ALL ;

	IN tmpMaster



*-- Code used prio VFP9

?SECONDS()

Here is the cascade update code used prio to VFP9 :

*-- Cascade update

SELECT tmpMaster

SCAN

  SELECT tmpChild

  LOCATE FOR FK == tmpMaster.OriginalPK

  IF FOUND()

    REPLACE FK WITH tmpMaster.PK

  ENDIF	

ENDSCAN



?SECONDS()

Replace the code after *-- Cascade update comment with the code below and run the code again.

Update - SQL in VFP9

?SECONDS()

UPDATE tmpChild SET FK = ;

	(SELECT PK FROM tmpMaster WHERE OriginalPK == tmpChild.FK)



?SECONDS()

You may found that Update-SQL in VFP9 work 10X faster than the code used prio to VFP9.