REPEAT..UNTIL break condition

rossi

Hi!

I have some problems with positions in the recordset. The record in scope is allways what_i_want - 1

I use this code to position my cursor and expect the Table.NEXT statement not to be executed when I have found the record I'm looking for.


REPEAT
IF Table.Field = what_looking _for THEN
found := TRUE
UNTIL (found=TRUE) OR (Table.NEXT(-1)=0)
...

IF found = TRUE THEN
use Table.Field.value (but the wrong one ..)
...


Is C/AL processing everything in the UNTIL or break when first hit a false?

Rossi

bbrown

The system must evaluate the entire UNTIL statement to determine if it is TRUE or FALSE.

ara3n

iF found = TRUE THEN begin
Table.NEXT;
use Table.Field.value (the right one ..)
end;

rossi

Hi again and thanks for every reply!

Acording to the documentation it should be possible to BREAK a repeat loop, but the compiler does not allow me ( stupid compiler...)

I want so much to have the record I found in the repeat loop in scope in further processing. I'm processing a lot of records and I want to skip as much overhead parsing as possible.

Any suggestions?

Rossi

bbrown

REPEAT
IF Table.Field = what_looking _for THEN BEGIN
found := TRUE;
Table2 := Table;
END;
UNTIL (found=TRUE) OR (Table.NEXT(-1)=0)
...

IF found = TRUE THEN
use Table2.Field.value (Use This record variable)

rossi

Hi again and thanks for participating my problem

So : the solution is to make a local var local_rec and use local_rec.COPY(found_rec) when i found my record and use the local_rec in further processing.

How does this statement impact my speed needs? In 99,99 percent i check the needed record first. Is a COPY a byVal or ByRef..

Again: any suggestions kill nighttime work

Referenc

bbrown

Since you are only going to copy 1 record (your found record) its impact on your process should be minimal. The copy is ByVal. Changes made to Table2 do not effect Table.

ara3n

COPY is byval

I don't think anything in navision is by reference, except paramaters for functions.

rossi

Thinking..

The COPY statement gives me the all records looping through.
Would it be btter if i can retrieve the primery key field(s) and use a GET and use local_rec

Hmmm...

bbrown

I suggested using
Rec2 := Rec1;
not
Rec2.COPY(Rec1);

rossi

Hi again

The rec_furter := rec_found work judt as i wnat.

Thanks a lot!

Not employeed, call me!

Rosi

SteveO

You could, of course, have also used filtering to achieve this.


or:

found := false;
EOT := false;

Table.FIND('+');

while (not found) and (not EOT) do begin
  found := (Table.field = check_value);
  if not found then
    EOT := (Table.NEXT(-1) = 0);
end;

if found then begin
  ... Do something
end;

ara3n

SteveO
It is realy bad practice to loop through recods with while statement. It has horrible performance compared to repeat until statement.

bbrown

There is no direct way to break loops.

You can break a loop by placing the loop in its own function and using an EXIT statement within the loop.

ara3n

That's a very good trick. Maybe you should put in the tips and trick forum. =D>

DenSter

BlackTiger wrote:

It is realy bad practice to loop through recods with while statement. It has horrible performance compared to repeat until statement.

Absolutely NOT TRUE. There is NO DIFFERENCE between REPEAT and WHILE in performance, only in logic.

I think what Ahmed means is that it is bad practice to do WHILE MyRec.FIND('-') DO. That does have horrible performance. From a purely logical standpoint the only difference between WHILE and REPEAT is that with one you check for something before you do an action, and with the other you do that after the action.

Luc_VanDyck

bbrown wrote:

There is no direct way to break loops.

You can break a loop by placing the loop in its own function and using an EXIT statement within the loop.

It will be easier to understand if you give us a code example.

bbrown

Record1 = Global Rec Variable

IF BreakTest THEN BEGIN...(Use Record1)

---

Function BreakTest : Boolean

IF Record1.FINDSET(TRUE, FALSE) THEN
REPEAT
IF (Condition True) THEN
EXIT(TRUE);
UNTIL Record1.NEXT = 0;
EXIT(FALSE);

---

girish.joshi

Denster wrote:

WHILE MyRec.FIND('-') DO

Denster,

I'm not sure that's what ara3n meant... because unless you are settign additional filters in the loop, or deleting records, the code you wrote will be an infinite loop ( poor performance indeed ) or always false.

ara3n -- what did you mean?

bbrown

This is not an infinite loop. It will either exit when it reaches a record that meets the condition, or when it reaches the end of the record set. Setting filters will only reduce the number of records processed.

This is only a basic example. It would of course be used along with code that set proper filters and keys on Record1 before calling the function.

NOTE: Condition True is just a placeholder in the example. In actual code the programmer would replace this with a actual statement to be evaluated as True or False.

bbrown

BlackTiger wrote: This is just a refactored copy of my code.

What are you referring to?

SteveO

It's also kind of bad practice to loop backwards through a table. But no comment was made there.
Sort order should be Descending and then read forwards through the table (maybe only for SQL this might not matter on native). I had heard that it could degrade performance if you do reads in a While loop as opposed to a Repeat but I have yet to see solid evidence of this (as opposed to anecdotal). I agree that WHILE (MyRec.FIND('-')) DO is bad practice as I know that this does have performance issues. But this is entirely different from what I had suggested.

The first thing I said was that filtering should have been used!! I only offered the other solution as an alternative. I don't understand why go through all this unnecessary code when simple filtering will do the trick.

bbrown

Navision is using SQL cursors to retrieve and process records. Unless the cursor is designated as Forward_Only, its records can be fetched in either direction, even randomly.

ara3n

Hello Sorry for not being clear.
Here is the quote from SQL server resource Kit pdf file.

The Navision client reads record by record (ISAM). Navision Server is designed to work this way but SQL Server is not. The SQL Server Option for Navision has been designed to detect when it is reading in a loop or reading single records. When it detects that it is reading single records only, it switches to singleton queries such as SELECT TOP 1 instead of set-based queries with subsequent fetches
This mechanism is disturbed if the application modifies a key in the index that the loop is based on or if the WHILE FIND(‘-‘) method is used instead of the REPEAT UNTIL NEXT method. Furthermore, the SQL Server query optimizer is often disturbed by this and frequently switches to another non-clustered index scan or clustered index scan execution plan. The Navision Server is robust in this scenario.

Never use WHILE FIND(‘-’) or WHILE FIND(‘+’) structures.
WHILE FIND(‘-’) or WHILE FIND(‘+’) logic is always used to look for the first or last record in a set and therefore automatically disables the read ahead mechanism. It is therefore recommended that you do not use this logic unless you have some very good reasons for doing so.
Example of bad code:

Customer.SETCURRENTKEY(“Currency Code”); Customer.SETRANGE(“Currency Code”, ‘GBP’);
WHILE Customer.FIND(‘-‘) DO BEGIN
Customer.“Currency Code” := ‘EUR’;
Customer.MODIFY;
END;

and looking at his code, I don't think the read ahead mechanism will be enabled.

bbrown

This mechanism is disturbed if the application modifies a key in the index that the loop is based on

Have you seen anything on how the new FINDSET(TRUE, TRUE) command impacts this?

Never use WHILE FIND(‘-’) or WHILE FIND(‘+’) structures

This will cause SQL to teardown and rebuild its cursor each time. This can have a sizable peformance impact.