Speed: temp Rec vs. Rec

abartonicek

Is there any performance gain in using temporary records instead normal records when I want to do some data investigation by using nested loops?

I tried both on G/L Entry but I was surprised to see that by using temp records the speed was 2x slower! (not counting the filling of the temp rec) :?:

First I have to fill the temp record (repeat..until next 0)
then I can use it, right?

The rest of the code was the same as in normal record case!

I thought that temp rec should be quicker!
Did I overlook something or am I doing something totally wrong?

Marije_Brummel

The temptable data is built op on your local system instead of on the server. Maybe you can try using a faster client.

bbrown

Paging of virtual memory on your workstation can impact temptable performace. In particular working with large temp tables.

abartonicek

So, in short, when I'm testing/doing something using my local client I can't get any speed boost using temp tables!

I hoped that temp tables would be faster because the whole table would be in system memory but I was too optimistic or I just need more RAM

ajhvdb

No, it's always slower. You can speed it up a bit by setting the local cache to 100000

abartonicek

My Windows pagefile (virtual memory) is 1024MB and Navision Object Cache (KB) = 50000, DBMS Cache (KB) = 150000, Commit Cache = Yes.
Can I do anything else?

Marije_Brummel

Maybe we'd better start with the question we should have started with in the first place:

What is the reason you are investigating this? Do you have performance issues? Or are you just interested in the subject?

abartonicek

Just interested, I guess
We had some problems before so we are checking some data from time to time. For this we have to use nested loops so speed boosts are always welcome

BTW, we have some strange problem, or something that is going to become a problem.
When we go on:
General Ledger -> Registers ->Menu Button "Register" -> General Ledger
we get form (dialog window...) that looks like this:

Searching the G/L Entry table, press Ctrl+Break to cancel... 49824
Counter. . . . . . . . . 49824
Table . . . . . . . . . . G/L Entry
Key. . . . . . . . . . . Document No. G/L Account No. Docum...

Why is that so? This started few week ago, and as the No. of entries in that table increases it takes longer and longer to get rid of that form.
Some times it pops up when we want to search the G/L Entry or any other (little bigger) table.
Our G/L Entry table currently has 200.000 entries!

Marije_Brummel

Is codeunit 235 - G/L Reg.-Gen. Ledger changed on your system?

or has someone changed the default sorting of form 20?

bbrown

This message is usually a result of a bad key selection. Has the form been modified? Or the table?

200,000 GL Entry records is fairly small. We have one customer with over 8,000,000 GL Entry records and probably even larger ones.

abartonicek

Yes, Form 20 is changed - sorting is by Document No, G/L Account No - requested by our client

I know 200.000 is small, that is way I'm worried.

Marije_Brummel

Maybe you can try changing the key by code in codeunit 235; That may overrule the key set in the form.

OItherwise you can make a copy of form 20, reset the key and run this from codeunit 235 instead of the default form (20)

abartonicek

I have to use that different key so I don't think I want to overrule that key in codeunit 235.
BTW, the form I'm running IS a copy of form 20 with that different key and some other, minor, changes.
So, because of that non primary key, the performance of that form is suffering.