Temporary table occupation

Belias

AFAIK, temptables are saved in RAM.
does anyone know how to calculate how much RAM does a single record in a table takes?
It seems too easy that is equal to the record size... :-k
Thanks in advance

garak

As i know, Navision 3.x will max. allocate 300K for each temp table since 4.0, as i know, it is 500k
.
It alloc. with the first insert command and cont. up to the limit with every following modifications.

If this max Memory usage is finsihed, Nav writes a TempFile to the Disk. So your local Disk should also be fast for writeting. But here is also a restriction. NAV can only write 2GB files. If your "temptable" is to big (greater then 2GB) u became an errormessage with the words: "Error 131 - An attempt was made to move file pointer before beginning of file" or so ....

As you know, the memory will be freed until the table handle is finished / closed.

Hope that is correct.

Regards

Belias

thanks for the useful info (now i also remember that for this reason client monitor crashes at 2GB)
but let's say i have a table with 300 records, how can i calculate how much memory this table will take?
maybe it will be 300*(sum of the lenght -in terms of kilobytes- of the fields)?
Hope 2 be clear...i can't explain it better on friday evening

garak

you can test it .... integer need 4 bytes, decimal 12, text use the textlenght + 1, option and bool also 4 and so on .....

kine

do not forget to add size of keys and SIFTS...

Belias

kine wrote:

do not forget to add size of keys and SIFTS...

This is what i was worried about...
Anyway, i've another question: what if i create and fill an array of temptables? will I have 500kb ram available for each element in the array?(in order to don't swap memory)

Maybe you'll be asking yourself why am i asking these questions, and the reasons are:
- Improve my knowledge
- Try to improve a slow process: i have made all possible modifications to the C/AL code, and i can't see any bottlenecks in client monitor: the slowest operations a lot of find/next (16ms). The problem is that this table is the item ledger entry, which is not so huge in my database...but there are a lot of loops on this table.
For this reason, i want to store the item ledger entry in a temptable and loop on it, which is (obviously) really faster in my test environment...what can you (all) say?

P.S.: i think that 16ms for a find/next can be a problem...but i can't access sql server to further investigate it
P.P.S.: key selection is correct for all sets of filters

kine

If there are mny loops and mainly nested loops, biggest performance gain will be to change the logic in a way to not have these loops or have minimum number of loops. Better is to have on e big loop than more nested. It means, if you are calculating something for some type of entries, than something other for another type etc. it is better to go through the join of all types you need in one loop and calc the value into some temporary table or array based on the entry type.

Just for ilustration simplified example:

  SETRANGE("Type", Type::"0");
  if FINDSET then
  repeat
    myVar1 := myVar1 + Amount;
  until NEXT=0;

  SETRANGE("Type", Type::"1");
  if FINDSET then
  repeat
    myVar2 := myVar2 + Cost;
  until NEXT=0;

  SETRANGE("Type", Type::"2");
  if FINDSET then
  repeat
    myVar1 := myVar1 - Amount;
  until NEXT=0;

could be replaced by

  SETRANGE("Type", Type::"0",Type::"2");
  if FINDSET then
  repeat
    case Type of
      Type::"0": myVar1 := myVar1 + Amount;
      Type::"1": myVar2 := myVar2 + Cost;
      Type::"2": myVar1 := myVar1 - Amount;
    end;
  until NEXT=0;

It is simplified, but same "logic" could be used for nested loops and complex filters. Rule is: if you already have read the record, do everything you need to do with it.

kriki

If you have a record-variable used as a temptable and put an array on it, the different elements will ALL point to the SAME temptable!
So this is not the way to increase the memory limit. You would need other variables.

Belias

kriki wrote:

If you have a record-variable used as a temptable and put an array on it, the different elements will ALL point to the SAME temptable!
So this is not the way to increase the memory limit. You would need other variables.

what a pity!
anyway...with the use of temptables (and some other minor fixes), i managed to bring the process time of the report from 1h52min to 15min...this is a GREAT improvement...i can swap memory without problems...now the real problem is how to avoid the 2GB limit...any idea?

@kine: unfortunately, i didn't developed this report, and i don't know the precise logic of it...as i'm doing this thing during the spare time, i can't ask the original developer...reverse engineering is not an option, as it's a bit complicated...up to now, i've modified the code without changing the logic (some ISEMPTY instead of FINDSET when i don't need rec values etc.)...I've already search for multiple (and/or useless) database calls as you suggested, but i couldn't find anyone...

P.S.: i don't think that pasting the code will be useful, as it is a report and you should import the text file in order to check it (if you want )...more than this, this report is part of an add-on and i don't think it should be correct to post it...

*EDIT: What if I process a block of records (there is a minor loop of "30 or so" turns on responsibility centers on the top of the process), delete the temptable, and refill it each turn?Is the memory freed?(i don't know how to test it by myself, sorry)

kriki

You have to insert a lot of records into a temptable before you hit the 2GB limit!
With version 2.01 I inserted 1.6MB ILE-records in a temptable without hitting the limit! (It was only VEEEEERRRRYYYY slow because of the writing to disk).

You can limit the size of the records by defining a temptable with ONLY the necessary fields and indexes.
AND(!) if you use a table ONLY as temptable, you don't need to put it in the customer's license!!!!!!!!

Belias

You can limit the size of the records by defining a temptable with ONLY the necessary fields and indexes.

#-o How stupid to not think about this...
anyway...i'm trying to overload my temptable in order to see if i can delete unused records to free some space

garak

thats the reason why in NAV exist Buffer tables ;-)

Sinse NAV 2.00 i created my own Buffertable(s) for Customer Solutions where a need a TempTable. So, Table 50000 .. 50005 (for example) only Buffertables for some temp. Records.

Belias

what do you mean with buffer tables?tables to flush a pieces of temporary table data before deleting it form the temptable?
(and also manage different keys, for sure)

kriki

Belias wrote:

what do you mean with buffer tables?tables to flush a pieces of temporary table data before deleting it form the temptable?
(and also manage different keys, for sure)

Buffertables are used by standard NAV to do some things like posting invoices. They are created as REAL tables (and their names finish with "Buffer") but they are ONLY used as temptables.

David_Singleton

Create another company in the same database. In this company, (using changecompany) populate the temporary table you are using with real values and insert into the database.

Backup the new company.

Create a small Navision database.

Import the backup. (You don't need objects, just the company data).

Look at Database Information tables. Find the table (it should be the only table in the list.

Here you will have all the statistics on the average size of the table, including keys and sift fields. It want be exactly the same in the temptable, but pretty close and will then let you know approximately how much ram you will need. Also you can write routines to drop fields you don't need to see the effect on size this will have.

Belias

David Singleton wrote:

Create another company in the same database. In this company, (using changecompany) populate the temporary table you are using with real values and insert into the database.

Backup the new company.

Create a small Navision database.

Import the backup. (You don't need objects, just the company data).

Look at Database Information tables. Find the table (it should be the only table in the list.

Here you will have all the statistics on the average size of the table, including keys and sift fields. It want be exactly the same in the temptable, but pretty close and will then let you know approximately how much ram you will need. Also you can write routines to drop fields you don't need to see the effect on size this will have.

Ok, nice trick...i'll try it!

kriki wrote:

Belias wrote:

what do you mean with buffer tables?tables to flush a pieces of temporary table data before deleting it form the temptable?
(and also manage different keys, for sure)

Buffertables are used by standard NAV to do some things like posting invoices. They are created as REAL tables (and their names finish with "Buffer") but they are ONLY used as temptables.

ok, then my question is: why don't declare the real table as temporary and use that table instead of creating a new one?is there something wrong with sifts and/or indexes, or is it only a "common practice" in order to not forgot the "Temp" property and make a disaster?

Thank you all for the valuable infos! =D>

David_Singleton

Buffer tables in Navision are not so much for performance (though as a side benefit they do marginally help posting speed), their prime purpose is as a simple way to restructure data prior to posting. Creating them as permanent tables would be problematic because of users, and would be unnecessary. As an example, a buffer is used to merge all the postings to one particular GL account into one entry prior to creating the GL Journal that will post. So you don't have one sales GL entry per sales line but instead per product group.

David_Singleton

By the way, I think you may be focusing a lot of your efforts in the wrong area. Of course you will get better performance by using Temp tables IFF it makes sense. But I think that Kamil's advise is more important. You need to focus on WHY the routine is making multiple reads on the same table, rather than making those reads faster.

MBerger

David Singleton wrote:

Creating them as permanent tables would be problematic because of users, and would be unnecessary.

When i DO need a real buffer table ( for displaying some derived date in a form or something ), i add a GUID for that session so the data of users don't mix ( USERID doesn't have to be unique amongst all sessions ), and a date so i can delete orphaned records ( sometimes people don't close the form properly, and data stays in the table. Deleting all records that are 2 days old will make sure your table doesn't get too filled ).

Belias

@David: you're right, creating huge temptables is not the "final" solution, in fact i used this method as a last resource.
The first thing i've done have been to find unnecessary loops...and i couldn't find them
Anyway, i'll try to search more carefully these loops... :-k

David_Singleton

MBerger wrote:

David Singleton wrote:

Creating them as permanent tables would be problematic because of users, and would be unnecessary.

When i DO need a real buffer table ( for displaying some derived date in a form or something ), i add a GUID for that session so the data of users don't mix ( USERID doesn't have to be unique amongst all sessions ), and a date so i can delete orphaned records ( sometimes people don't close the form properly, and data stays in the table. Deleting all records that are 2 days old will make sure your table doesn't get too filled ).

Then this is not a "buffer table" in this sense.

In your case why are you using a permanent table, it seems a temporary table would do what you need just fine.

MBerger

David Singleton wrote:

In your case why are you using a permanent table, it seems a temporary table would do what you need just fine.

Because in NAV < 5.0 you can't display temptables in a form.

David_Singleton

MBerger wrote:

David Singleton wrote:

In your case why are you using a permanent table, it seems a temporary table would do what you need just fine.

Because in NAV < 5.0 you can't display temptables in a form.

:-k who told you that? I have been doing this since 1.1, never tried it in 1.0, but I assume it worked there also.

Belias

MBerger wrote:

David Singleton wrote:

In your case why are you using a permanent table, it seems a temporary table would do what you need just fine.

Because in NAV < 5.0 you can't display temptables in a form.

not really...you can call the form from code:

myrealtable.find('-');
repeat;
  mytemptable.init;
  mytemptable := myrealtable;
  mytemptable.INSERT;
until myrealtable.next = 0;
FORM.RUNMODAL(0,mytemptable);

and this is not the only method...anyway, as I said, i'm doing this report during spare time...i'll let you know after some tests if and how i improved the process (buffer tables apart )

Belias

The report i'm trying to optimize, is part of the database i've described here:
http://www.mibuso.com/forum/viewtopic.php?f=33&t=36521&view=unread#unread
I think it will perform a lot better after some (...a lot!) of basic SQL tuning...thank you all to have stopped me in doing this uber temptable, i'll let you know how is it after the tune!

philipuskd

When a table is growing very large in size (some GB) and intensively Read / write then I need the structure of the table for a temporary table variable, in terms of performance, which one is better? Using the existing table for the declaration of my temp table variable or create a new table with the same table description ("save as" from the existing table) and declare the temp table variable using the new empty database?

Will the Instantiation of a temp table variable affect the I/O performance of the table?

kine

Desn't matter. Will work in same way, having no effect to performance. Only difference could be that the table object could be small, because you will remove the code, thus loading the object will be quicker, but this is done once and object cache will keep the object when needed, thus not so big gain...

kriki

And if you only keep the fields you really need, the temptable will be smaller again.

And if you ONLY use it as a temptable,you don't need to put the table in the license!

Belias

and at last, if you do some cleanup on the temptable during process with a DELETEALL, you can avoid to burn the entire source table (in case you forgot set Temporary=true on the variable)
:whistle: :whistle: :whistle: But this is never happened to me... :whistle: :whistle: :whistle:

philipuskd

Thanks for the update.

Kamil, I'm just being worried that when the C/AL code reads the structure of the physical table for the instantiation, this will somehow make another process to wait until the reading finished or when the table is locked by another user / process, the instantiation process will wait until the lock is released. This table I mentioned is really big also being read and written so often. So I'm thinking that even using the table to declare the temp table will somehow add a slight loads to the table, isn't it?

kine

The whole process of working with temp table is working in Native DBMS (like when working with native DB) and is done locally on the client. No connection with anything on SQL...