How is BEST PRACTICE in a FOR EACH sentence?

[erickroco9611]

Which is BEST PRACTICE...!!??

for each po
where po.whs-code = "10"
and po.cust-no >= 1000
and po.cust-no <= 9999
and po.dt-cancel = ? no-lock:
.... [PROCESS]...
end.

OR

for each po
where po.whs-code = "10"
and po.cust-no >= 1000
and po.cust-no <= 9999 no-lock:

if po.dt-cancel <> ? then next.
.... [PROCESS]...
end.

OR

!!! Maybe you have a better idea !!!

Considering that po.dt-cancel isn´t part of any INDEX in the table.

In the first example how can affect the performance?

Thak you very much... I really appreciate your time!!!

 

[TomBascom]

I prefer the 1st form.

The extra field in the WHERE clause does not negatively impact performance.

If you later add an index that would allow the dt-cancel field to be used then the compiler will just pick it up. You also avoid the possibility of code accidentally slipping between the FOR EACH and the IF. (Of course if code needs to be there then option 2 is your only choice...)

If you do add an index to support this query then whs-code and dt-cancel should come before cust-no because they are equality matches whereas cust-no is a range match.

 

[erickroco9611]

Thanks TOM.. It was really helpfull!!!

I had that dubt, but now I have the opinion of an expert!!!

 

[lord_icon]

Greetings,
I would have thought that the first 1 is also better and more efficient.
Only the required records are gathered into the buffer because of the WHERE clause. It is specifically fetching the records that match the criteria in the first pass. The second method gathers the records that match the criteria, then looks at them again to satisfy the condition in the statement. Which is a dirty method, I am in agreement and would much advise to use the first method.

 

[hramos]

I think it´s better this way

for each po
where po.whs-code = "10"
and (po.cust-no >= 1000
and po.cust-no <= 9999)
and po.dt-cancel = ? no-lock:
.... [PROCESS]...
end.

 

[TomBascom]

Better how?

 

[lord_icon]

Greetings,

To jump in and answer Tom`s question.
It is better in that it is more aesthetically pleasing. As an advanced business application architect, it is a method which makes to be more readable.

 

[RealHeavyDude]

Just one note:

!!! This might not apply to your example !!!

Depending on your indexes and setting the brackets in a query, it may result in poor performance or in good performance. So, IMHO, setting brackets to make code better readable may is not always be the best advise. As always, it depends - in the worst case you will end up doing an unnecessary table scan because of setting brackets in your code in the wrong place ...

Regards, RealHeavyDude.

 

[TomBascom]

Well in that case we should enclose it in CODE tags and format it properly

Here's my "style":

for each po no-lock where
    po.whs-code = "10" and
    po.dt-cancel = ? and
    ( po.cust-no >= 1000 and po.cust-no <= 9999 ):

  /* ...... */

end.

 

[GregTomkins]

If the client running the FOR-EACH is remote from the client running the DB, is the former method more efficient (eg. how much, if any, of FOR-EACH's filtering logic happens on the DB server?)