The 'best' way to update a table & records... 1

[brigmar]

I've only just been introduced to FoxPro, so I'm not sure how it optimizes..

I have to do a blanket update to some fields for all records in a table, and then a record-by-record update of some fields based upon certain logic.

I see 2 ways of doing this:

1/
Use a REPLACE .. ALL command for the blanket updates
Then a SCAN..ENDSCAN loop with the logic inside for the record-by-record updates.

2/
Putting everything inside the SCAN..ENDSCAN loop, and modifying the REPLACE command to include the fields I previously put in the REPLACE ALL command.

Method 1 seems (to me) to break out better logically (ie. Do THIS to the entire table, and then work out THAT with each record), but I'm aware that I'd be scanning the table twice.

What's the accepted 'wisdom' on this issue ?

 

[DSummZZZ]

The major limitation with replace all, is that you need exclusive access to the table. It can't be shared. So your only choice is to do a SCAN or DO WHILE loop.
But if this is a one-time deal and you can open the table exclusively, replace all will probably only take a few seconds to update the entire table. You can then do your SCAN process after that.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[brigmar]

Thanks.
I am guaranteed EXCLUSIVE access to the table, as it is a unique staging table that I have created.

Is it quicker to do the following (for example):

method 1:
---------
REPLACE emailtag WITH .T. FOR LIKE(data,"*@*.*") ALL
REPLACE phonetag WITH .T. FOR LIKE(pattern,"9999999999") ALL
SCAN
/ other more complex checks here
ENDSCAN

as opposed to..

method 2:
---------
SCAN
REPLACE emailtag WITH .T. FOR LIKE(data,"*@*.*")
REPLACE phonetag WITH .T. FOR LIKE(pattern,"9999999999")
/ other more complex checks here
ENDSCAN

method 1 - involves 3 scans through the table
method 2 - 1 scan through the table, but each scan involves more operations.

I'm used to working with set based operations, and reverting to 'cursors' only when necessary. I'm seeing resistance from the FoxPro guys here complaining that I'm scanning the table multiple times. My argument is that I'm performing less operations per scan.

I'm assuming there's a speed tradeoff that's a function of the number of records, and the number of fields being updated....

What's the best way to go (with FoxPro) ?
Set based or cursor based ?

 

[DSummZZZ]

Again, if this is a one shot deal, do the REPLACEs separate. Who cares how many times you go through the table? If this is a process you will be running regularly, then write it in the easiest to maintain.

Anyway, if you want to do it in one pass, just do it this way:

SCAN
   IF LIKE(data,"*@*.*")  
      REPLACE emailtag WITH .T. 
   ENDIF
   IF LIKE(pattern,"9999999999")
      REPLACE phonetag WITH .T. 
   ENDIF
  / other more complex checks here
ENDSCAN

But Mike is right. You don't want to do any sort of global updates like ALL or FOR within a control loop such as SCAN, as each time it loops around, it will execute the operation for the entire table. Not to mention leaving you record pointer - well, no telling where.

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[brigmar]

My mistake with cut and paste there. Sorry.

I think I meant to change the FOR to a WHILE for the UPDATE command inside the SCAN.

That would be the equivalent to putting the UPDATE inside an IF construct, no?

Thanks for the input. I'll likely have more questions come tomorrow... and the day after

(never used FoxPro until a month ago, and having to write for FPW 2.6a)

 

[DSummZZZ]

I think I meant to change the FOR to a WHILE for the UPDATE command inside the SCAN.

Let me reiterate something here.
Doing a SCAN...ENDSCAN runs through the table on a record per record basis. It replaces:

DO WHILE !EOF()
   ...
   SKIP
ENDDO

So if you are SCANing a table, and use a REPLACE ALL, FOR, WHILE, NEXT n and so on, you will inadvertantly move the record pointer. So when you continue the SCAN, it will pick up from the current record pointer position.
FOR will replace all records which match the criteria.
WHILE will replace records while, or as long as, the condition is met.
Neither is good to have in a control structure. Within a control structure, you should just test for the condition and update as needed.

That would be the equivalent to putting the UPDATE inside an IF construct, no?

Basically, you're right. UPDATE will act pretty much the same, except I believe it requires fields from the source table to be the same as the target. It works well for SQL syntax, but is really unnecessary for this scenario.
If you're running through the table anyway, just use a REPLACE for the fields you need to modify.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[brigmar]

You see ? Still can't get out of using SQL commands.
When I typed UPDATE, I meant REPLACE.