Options

Temporary table occupation

BeliasBelias Member Posts: 2,998
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
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
«1

Comments

  • Options
    garakgarak Member Posts: 3,263
    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
    Do you make it right, it works too!
  • Options
    BeliasBelias Member Posts: 2,998
    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
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    garakgarak Member Posts: 3,263
    you can test it .... integer need 4 bytes, decimal 12, text use the textlenght + 1, option and bool also 4 and so on .....
    Do you make it right, it works too!
  • Options
    kinekine Member Posts: 12,562
    do not forget to add size of keys and SIFTS...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    BeliasBelias Member Posts: 2,998
    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 :wink:
    - 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 :cry:
    P.P.S.: key selection is correct for all sets of filters
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    kinekine Member Posts: 12,562
    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.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    krikikriki Member, Moderator Posts: 9,098
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    BeliasBelias Member Posts: 2,998
    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! :cry:
    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) :cry:
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    krikikriki Member, Moderator Posts: 9,098
    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!!!!!!!!
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    BeliasBelias Member Posts: 2,998
    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
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    garakgarak Member Posts: 3,263
    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.
    Do you make it right, it works too!
  • Options
    BeliasBelias Member Posts: 2,998
    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)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    krikikriki Member, Moderator Posts: 9,098
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • Options
    BeliasBelias Member Posts: 2,998
    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>
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    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
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • Options
    MBergerMBerger Member Posts: 413
    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 ).
  • Options
    BeliasBelias Member Posts: 2,998
    @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
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    MBerger 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.
    David Singleton
  • Options
    MBergerMBerger Member Posts: 413
    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.
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    MBerger 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.
    David Singleton
  • Options
    BeliasBelias Member Posts: 2,998
    MBerger 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 :wink: )
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    BeliasBelias Member Posts: 2,998
    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!
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    philipuskdphilipuskd Member Posts: 36
    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?
  • Options
    kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    krikikriki Member, Moderator Posts: 9,098
    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!
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    BeliasBelias Member Posts: 2,998
    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:
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    philipuskdphilipuskd Member Posts: 36
    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?
  • Options
    kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
Sign In or Register to comment.