The nature of a temporary table

DeSp

I haven't met any detailed documentation describing a temporary table behavior, therefore I would like to clear a few things (any references would be appreciated).

What is the physical nature of a temporary table? How does it work? Considering Navision 3.7A SQL Server Option, how does a Navision client handles a temporary table? If there are several keys defined in such a table, then we declare a temporary instance of it, then will the client use these physical keys for quering data or simply scan through a temporary data set? If there are SIFT levels defined in these keys, will the client use them, or scan sums through a temporary dataset anyway?

There is a OLAP-like Navision report and it should work as fast as it possible. The fastest way we have come to - is to use a temporary table (using of non-temporary physical table is much slower, despite on all the optimizations and tunings done).
The question is to store or not to store a very large amount of records in a temporary table (~2 650 000 entries). This temporary table works faster, but we test the report on the database server with 4 Gb of RAM. Will a client PC with 128-256 Mb of RAM survive this? What are the restrictions for using a temporary table?

Any ideas and suggestions would be very much appreciated.

Thanks in advance.

lubost

1. You can only use existing table definition as temporare table.
2. Temporary table is defined by setting property in variable definition
3. Temporary table has all keys and code as there are defined in table definition
4. Temporary table is created on client side. Records are stored in temporare file on client. Access speed therefore depends on client environment

It is very hard to compare the speed of accessing temporare table and real table, because it depends on number of users accessing that table, locking filters and keys. Generally filling of temporary table need an extra time and better solution is to print the report from real table.
I only use temporary table in reports only for grouping purposes.

DeSp

Thanks for reply, lubost.

So a Navision client stores all data and index structures for defined keys of a temporary table in a temporary file on client side, despite on it could be enough RAM for storing it directly in memory? I think that while there is enough RAM on client side, a temporary table and its index structures should be stored directly in memory and then after exceeding some limits (object cache?) should be flushed on disk. You are saying that a temporary table has all keys defined in a table definition, but are they really used when quering data in a temporary table or they are useless and a simple scan through a temporary data set is always established?

Our report has complex OLAP-like grouping routines. The use of a real table for this report dramatically decreases performance.

kriki

I don't know for sure if the SIFT (in the form of CALCSUMS! but not CALCFIELDS) is used in temporary tables. But anyway a temptable is much faster than a real table. Even if your client has few memory, it is still faster.
Navision starts first working in memory and if needed creates a tempfile on the local disk (this means your tempdir MUST be a local disk for performance).
There is a limit on the size of the tempfile : 2 GB. If you go over that limit, Navision gives an error.

DeSp

There is a limit on the size of the tempfile : 2 GB. If you go over that limit, Navision gives an error.

Is it possible to override this limit somehow? Maybe there is some magic number that should be placed into $ndo$dbconfig or $ndo$dbproperty or somewhere else?

kriki

No, it is a old limit that comes from the FAT-times when a file could not be larger than 2GB. Navision hasn't removed the limit yet (I didn't test with 5.00).

jjanauskas

in addition to, you can use ANY table from ANY object number range for your temporary table. If I need some advanced temporary structures, I usually create a special table for that with a number 900xx and as a temporary table it works with all licenses.

Furthermore, keep in mind, that temporary table is working the same way as navision native database in sense of filters and keys (and it is slightly different from SQL database)

Markandey_Pandey

lubost wrote:

I only use temporary table in reports only for grouping purposes.

how it is done?

kriki

Markandey Pandey wrote:

lubost wrote:

I only use temporary table in reports only for grouping purposes.

how it is done?

See http://www.mibuso.com/howtoinfo.asp?FileID=22 point "Make a total amount per "Gen. Bus. Posting Group" and "Gen. Prod. Posting Group" of table 15:"G/L Entry".
".
and also : http://www.mibuso.com/howtoinfo.asp?FileID=6

Markandey_Pandey

kriki wrote:

See http://www.mibuso.com/howtoinfo.asp?FileID=22 point "Make a total amount per "Gen. Bus. Posting Group" and "Gen. Prod. Posting Group" of table 15:"G/L Entry".

Thanx, Actually i want to sum up "Quantity Per" field from "Bom Component" table based on "No." field and i am using "Bom Component" as temp table.

kriki

Markandey Pandey wrote:

kriki wrote:

See http://www.mibuso.com/howtoinfo.asp?FileID=22 point "Make a total amount per "Gen. Bus. Posting Group" and "Gen. Prod. Posting Group" of table 15:"G/L Entry".

Thanx, Actually i want to sum up "Quantity Per" field from "Bom Component" table based on "No." field and i am using "Bom Component" as temp table.

Well, the example is an example on how to proceed.

mkpjsr

kriki wrote:

Markandey Pandey wrote:

kriki wrote:

See http://www.mibuso.com/howtoinfo.asp?FileID=22 point "Make a total amount per "Gen. Bus. Posting Group" and "Gen. Prod. Posting Group" of table 15:"G/L Entry".

Thanx, Actually i want to sum up "Quantity Per" field from "Bom Component" table based on "No." field and i am using "Bom Component" as temp table.

Well, the example is an example on how to proceed.

I am not able to find appropriate key for grouping, and i have modified the code as

BomComp.RESET;
BomComp.SETCURRENTKEY(Type,"No."); 
BomComp.SETRANGE("No.","No."); 
IF BomComp.FINDSET THEN
  REPEAT
        TempBom.RESET;
 
     IF NOT TempBom.SETCURRENTKEY(Type,"No.") THEN
      IF NOT TempBom.SETCURRENTKEY(Type,"No.") THEN ;
      
        TempBom.SETRANGE("No.",BomComp."No.");

 
    IF NOT TempBom.FINDFIRST THEN BEGIN
           TempBom := BomComp;
      TempBom.INSERT(FALSE);
    END
    ELSE BEGIN
       TempBom."Quantity per" += BomComp."Quantity per";
      TempBom.MODIFY(FALSE);
    END;
  UNTIL BomComp.NEXT = 0;

but its not working,

kriki

Maybe you have used different variants or Unit of measures.
I added those fields.
But do you get an error, or is the output not what you expect?

BomComp.RESET;
BomComp.SETCURRENTKEY(Type,"No.");
BomComp.SETRANGE("No.","No.");
IF BomComp.FINDSET THEN
  REPEAT
    TempBom.RESET;
    IF TempBom.SETCURRENTKEY(Type,"No.") THEN ;
    TempBom.SETRANGE(Type,BomComp.Type); 
    TempBom.SETRANGE("No.",BomComp."No.");
    TempBom.SETRANGE("Unit Of Measure code",BomComp."Unit Of Measure code");
    TempBom.SETRANGE("Variant Code",BomComp."Variant Code");
    IF NOT TempBom.FINDFIRST THEN BEGIN
      TempBom := BomComp;
      TempBom.INSERT(FALSE);
    END
    ELSE BEGIN
      TempBom."Quantity per" += BomComp."Quantity per";
      TempBom.MODIFY(FALSE);
    END;
  UNTIL BomComp.NEXT = 0; 

mkpjsr

kriki wrote:

Maybe you have used different variants or Unit of measures.
I added those fields.
But do you get an error, or is the output not what you expect?

i am not getting any error but not getting the output the way i want.

Here what i want is, if an item is stored more than once then it should be displayed once and the Qunatity Per field should contain the sum of the items.
it should be like this:
For ex:

Suppose I have two finished good items FG1 and FG2, and both has bill of materials

FG1 comprise of say,
Componet Required Quantity Per
===============================
Component1

---

> 1
Component2

---

> 2
Component3

---

> 2
Component4

---

> 1

and, FG2 Comprise of
Component1

---

> 1
Component2

---

> 1
Component5

---

> 1
Component6

---

> 1

here Component1 and Component2 are common in both finished goods,
so the BOMs report should look like this,

Item Filter : FG1|FG2


Component Qty Required
================================
Component1

---

> 2
Component2

---

> 3
Component3

---

> 2
Component4

---

> 1
Component5

---

> 1
Component6

---

> 1

kriki

I changed a little the code:

BomComp.RESET;
BomComp.SETCURRENTKEY("Parent Item No.","Line No.");
BomComp.SETRANGE(your filters on the record you want to group);
IF BomComp.FINDSET THEN
  REPEAT
    TempBom.RESET;
    IF TempBom.SETCURRENTKEY(Type,"No.") THEN ;
    TempBom.SETRANGE(Type,BomComp.Type);
    TempBom.SETRANGE("No.",BomComp."No.");
    IF NOT TempBom.FINDFIRST THEN BEGIN
      TempBom := BomComp;
      TempBom."Parent Item No." := '';
      TempBom.INSERT(FALSE);
    END
    ELSE BEGIN
      TempBom."Quantity per" += BomComp."Quantity per";
      TempBom.MODIFY(FALSE);
    END;
  UNTIL BomComp.NEXT = 0;