Using PRESELECT EACH

[DevTeam]

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]

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]

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:

for each customer:
  custNum = custNum + 10000.
end.

Better:

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]

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.

 

[TomBascom]

I almost always use FOR EACH. And I pretty much usually use immutable keys.

 

[Dharmendra]

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:

for each customer:
  custNum = custNum + 10000.
end.

Better:

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]

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]

This is an old thread, so:

Bad idea:

for each customer:
  custNum = custNum + 10000.
end.

2019:

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

 

[jdpjamesp]

Your code is missing a lock statement.

 

[TomBascom]

So was mine

 

[KrisM]

Another thing that is bothering me in this code :

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

should be

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

 

[TomBascom]

Actually it should be:

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

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

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.

 

[Marco Mendoza]

Actually it should be:

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

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

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]

Yeesh! The errors just keep piling up!

On the bright side it is nice to see that you guys are actually reading this stuff

 

[TomBascom]

Corrected correction:

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.

 

[Marco Mendoza]

Yeesh! The errors just keep piling up!

On the bright side it is nice to see that you guys are actually reading this stuff

Always! Let's see if KrisM can see the other typo.

 

[TomBascom]

Groan. I found it

 

[Rob Fitzpatrick]

Corrected correction:

Might be even more correct with error-handling...
<ducking behind cover>

 

[jdpjamesp]

UNDO, THROW NEW ExtraSpikyErrorAtRob("Gotcha!",0).

 

[andre42]

Might be even more correct with error-handling...
<ducking behind cover>

-undothrow 2