Using PRESELECT EACH

DevTeam

Member
Hi,

Could someone explain to me what are the advantadges and / or drawbacks of specifying "PRESELECT EACH" instead of "FOR EACH" into an OPEN QUERY statement ?

TIA.

Julien
 

sunilnair

Member
FOR EACH starts an iterating block , in which for each iteration , the record is fetched from the database into the record buffer and processed as per the statements within the block.

PRESELECT EACH , does not actually fetch the record, it creates a temporary index to all the db records which match the record criteria , which can then be accessed using the FIND command.

For large tables with lots of processing ,a combination of PRESELECT and FIND Next may actually work faster than a FOR EACH.
 

TomBascom

Curmudgeon
PRESELECT gives you a snapshot -- IOW if you are changing the value of a field used in the index you won't see the record again.

Bad idea:
Code:
for each customer:
  custNum = custNum + 10000.
end.

Better:
Code:
repeat preselect each customer:
  find next customer.
  custNum = custNum + 10000.
end.

It isn't that FOR EACH is bad -- but if you update a field used in the selection of the records you can be surprised.
 

DevTeam

Member
It isn't that FOR EACH is bad -- but if you update a field used in the selection of the records you can be surprised.
Yep, already experienced that...

So, if we're dealing with a dynamic-query, which one (for or preselect) would you rather use in the QUERY-PREPARE statement ?

Thanks to both of you.
 

Dharmendra

New Member
PRESELECT gives you a snapshot -- IOW if you are changing the value of a field used in the index you won't see the record again.

Bad idea:
Code:
for each customer:
  custNum = custNum + 10000.
end.

Better:
Code:
repeat preselect each customer:
  find next customer.
  custNum = custNum + 10000.
end.

It isn't that FOR EACH is bad -- but if you update a field used in the selection of the records you can be surprised.

Hi Tom,

I have small doubt here.
So PRESELECT is nothing but copy of the db record and we fetch copy of the record using find?
 

TomBascom

Curmudgeon
I would not describe it that way.

PRESELECT makes a snapshot of the result set at the point in time that it is first invoked. To access the records in the snapshot you use FIND.

How it does that behind the scenes is not the point of my comments.
 

Bounty

New Member
This is an old thread, so:

Bad idea:
Code:
for each customer:
  custNum = custNum + 10000.
end.

2019:
Code:
for each customer TABLE-SCAN:
  custNum = custNum + 10000.
end.
 

KrisM

Member
Another thing that is bothering me in this code :

Code:
for each customer no-lock:
  custNum = custNum + 10000.
end.

should be
Code:
for each customer no-lock:
  customer.custNum = customer.custNum + 10000.
end.
 

TomBascom

Curmudgeon
Actually it should be:
Code:
for each customer exclusive-lock:
  customer.custNum = customer.custNum + 10000.
end.

... or, if we want to tightly control transaction scope, perhaps:

Code:
define buffer updCustomer for customer.

for each customer no-lock:
  do for customer transaction:
    find updCustomer exclsuive-lock where recid( updCustomer ) = recid( customer ).
    updCustomer.custNum = customer.custNum + 10000.
  end.
end.
 
Actually it should be:
Code:
for each customer exclusive-lock:
  customer.custNum = customer.custNum + 10000.
end.

... or, if we want to tightly control transaction scope, perhaps:

Code:
define buffer updCustomer for customer.

for each customer no-lock:
  do for customer transaction:
    find updCustomer exclsuive-lock where recid( updCustomer ) = recid( customer ).
    updCustomer.custNum = customer.custNum + 10000.
  end.
end.


" do for /*customer*/ updCustomer transaction:"
 

TomBascom

Curmudgeon
Corrected correction:

Code:
define buffer updCustomer for customer.

for each customer no-lock:
  do for updCustomer transaction:
    find updCustomer exclsuive-lock where recid( updCustomer ) = recid( customer ).
    updCustomer.custNum = customer.custNum + 10000.
  end.
end.
 
Top