REPEAT extremely slow!!!

gerba

Hi all!

It's exasperating...
I am running through a filtered record using the REPEAT construct.
And I was trying to figure out, why my code is so slow.
Actually not the code within the REPEAT construct is slow - each loop takes only between 0 and 31 milliseconds.
It looks like:

REPEAT;
// some code
UNTIL myRecordVar.NEXT = 0; // ->> more than 10 milliseconds each.

i.e.: When I take the time from before REPEAT to after UNTIL it is 971 milliseconds; in contrast, when I take the time from after REPEAT to before UNTIL and sum all taken times it is only 124 milliseconds. REPEAT loops through 77 times. That means that "looping" itself takes 847 milliseconds, that is 11 milliseconds per UNTIL.

What can be the reason for REPEAT being that slow?!

Thx alot for your help in advance!
Gerald

Malajlo

Try changing more suitable key if you loop on filtered table.

kriki

If you're on SQL, you can also try FINDSET instead of FIND('-') (NEVER use FINDFIRST for looping). (you didn't specify SQL/native or FINDSET/FIND('-').

gerba

Thank you for the quick reply!

Why is speed of NEXT depending on the used key?

Gerald

gerba

@kriki:

I am using native and FIND('-').

kriki

gerba wrote:

Thank you for the quick reply!

Why is speed of NEXT depending on the used key?

Gerald

I explain it with an example:
If you have 100000 records in a table and you have to find 10 of them.
If you have a key that directly finds the records, the DB scans only 10 records to find them.
If you use a wrong key, it is possible that the DB has to scan all 100000 of them, resulting in poor performance.

PS1: Can you also post the filters you put on the fields?
PS2: Are there filters on flowfields?

gerba

@kriki:

First: How can a key "find" records?

PS1: I use only "simple" filters on Boolean- or Option-Fields, except this one:
myRecordVar.SETRANGE("Manuf. Date",0D,ToDate);
'ToDate' is a variable containing a Date.
Can that really slow down the process?

PS2: There are no filters on FlowFields. What impact would that have after a CALCFIELDS?

kriki

gerba wrote:

@kriki:

First: How can a key "find" records?

PS1: I use only "simple" filters on Boolean- or Option-Fields, except this one:
myRecordVar.SETRANGE("Manuf. Date",0D,ToDate);
'ToDate' is a variable containing a Date.
Can that really slow down the process?

PS2: There are no filters on FlowFields. What impact would that have after a CALCFIELDS?

First: Keys are created to find records in a fast way. Example take the phone index and think how difficult it would be to find the phone number of a person if everything was ordered by the primary key (=the phone No.).

PS1: can you also give the filters on the other fields?

PS2: CALCFIELDS would not help. The filter on a flowfield would automatically trigger that. But for each record, NAV would have to calculate the value of the flowfield and that slows down a lot.

gerba

PS1:

myRecordVar.SETCURRENTKEY(Art,"Nr.",Bereich,Belegart,Finished,ReleasedForPurchaseRequest,"Manuf. Date");
myRecordVar.SETRANGE(Bereich,myRecordVar.Bereich::Verkauf);
myRecordVar.SETRANGE(Belegart,myRecordVar.Belegart::Auftrag);
myRecordVar.SETRANGE(Art,myRecordVar.Art::Artikel);
myRecordVar.SETRANGE("Nr.",prcItem."No.");
myRecordVar.SETRANGE(Finished,FALSE);
myRecordVar.SETRANGE(ReleasedForPurchaseRequest,TRUE);
myRecordVar.SETRANGE("Manuf. Date",0D,ToDate);
IF myRecordVar.FIND('-') THEN BEGIN

kine

And all depends on what you are doing inside the loop too...

gerba

kine wrote:

And all depends on what you are doing inside the loop too...

sorry, but no.
If I comment all code inside the loop, the whole loop process still takes about 847 milliseconds - that is the whole 971 ms without the 124 ms for the code inside the loop.

kine

Is it really slow? What about network? Discs? RAM? Cache? Next in 11 ms is not "slow"...

gerba

NEXT should take almost 0 ms as most of the time the code inside the loop also takes almost 0 ms.

kine

:-) not good deduction... If you take that into account, whole application will work in 0 time... 8)

NEXT reads from the database in some manner, rest of your code is doing what? Inserting? Modify? Just assigning into variable? Working with table with 3 records?

David_Singleton

gerba wrote:

NEXT should take almost 0 ms as most of the time the code inside the loop also takes almost 0 ms.

](*,) good luck.

gerba

kine wrote:

NEXT reads from the database in some manner, rest of your code is doing what? Inserting? Modify? Just assigning into variable? Working with table with 3 records?

The code inside the loop is just reading or processing the data of the fields - no modifying.
The table I'm running through has hundreds of thousands of records. So 11ms per record IS affecting time.

Waldo

I suggest to use the client monitor to see what SQL does. You can look at the execution plans that SQL uses ... . Did you already investigate that?

One tip: use the Client Monitor that from the TOOLS CD.

PeterD

What happens if you change the order to:

myRecordVar.SETCURRENTKEY(Art,"Nr.",Bereich,Belegart,Finished,ReleasedForPurchaseRequest,"Manuf. Date");
myRecordVar.SETRANGE(Art,myRecordVar.Art::Artikel);
myRecordVar.SETRANGE("Nr.",prcItem."No.");
myRecordVar.SETRANGE(Bereich,myRecordVar.Bereich::Verkauf);
myRecordVar.SETRANGE(Belegart,myRecordVar.Belegart::Auftrag);
myRecordVar.SETRANGE(Finished,FALSE);
myRecordVar.SETRANGE(ReleasedForPurchaseRequest,TRUE);
myRecordVar.SETRANGE("Manuf. Date",0D,ToDate);
IF myRecordVar.FIND('-') THEN BEGIN

kine

Waldo wrote:

I suggest to use the client monitor to see what SQL does. You can look at the execution plans that SQL uses ... . Did you already investigate that?

One tip: use the Client Monitor that from the TOOLS CD.

I am using native and FIND('-').

It is Native DB, SQL is doing nothing... :-) but the NAV Server must read the data somehow too... checking the client monitor can give more info...

Waldo

Sorry ... didn't read it in enough detail, apparently ... :oops:
But indeed ... the client monitor works the same, but doesn't give SQL info...

gerba

Hi all!

Increasing DBMS cache really improved speed a bit.
But the actual heavy slowdown was something totally different...
The DataItem in the report concerned was the Item table.
I found out, that at the end of the OnAfterGetRecord trigger there was a COMMIT. :roll:
Now the 7000 items are processed in 2min instead of 11min!
Incredible...

Thank you for all your help!
Gerald

David_Singleton

gerba wrote:

Hi all!

Increasing DBMS cache really improved speed a bit.
But the actual heavy slowdown was something totally different...
The DataItem in the report concerned was the Item table.
I found out, that at the end of the OnAfterGetRecord trigger there was a COMMIT. :roll:
Now the 7000 items are processed in 2min instead of 11min!
Incredible...

Thank you for all your help!
Gerald

It just goes to show that some time the experts do actually know their stuff, and listening to them can same you a lot of time AND MONEY.

As Kine pointed out right at the begining, you need to be open and explain the situation, rather than just deciding what the issue is without facts. As it was Kine was right, in that it was some other code somewhere causing the problem, but you refused to take his advise. ](*,)

gerba

Hi!

Sorry for any inconvenience!
The code we were discussing and of which I thought it was slowing down the process was in a totally different place than where I found the mentioned COMMIT.
I would have had to post several objects in this case.
But I think you'll understand that this was not possible.

All the same your tips where of much help, as I also wrote.
Thank you again!

Gerald

David_Singleton

gerba wrote:

Hi!

Sorry for any inconvenience!
The code we were discussing and of which I thought it was slowing down the process was in a totally different place than where I found the mentioned COMMIT.
I would have had to post several objects in this case.
But I think you'll understand that this was not possible.

All the same your tips where of much help, as I also wrote.
Thank you again!

Gerald

No need to apologize. I only pointed this out to try to help you next time. The point is that many of us have a lot of experience that beginners simply look at the first thing in front of them and concentrate all their efforts on what they get a fixation on. Its very important to open your mind and look further. Once you say "no that's 100% not the problem" then you are lost and probably are going to waste a lot of your client's money trying to fix a non existent problem.

Unfortunately the cost to implement Navision is going through the roof these days, and the core reason is inexperience in the channel. So when you get a chance to learn from a true expert like Kamil, don't just shrug off his advise, because one day his advise may save your client many thousands of dollars.