Temporary Tables and Memory

I have a temp table (Sales Lines). I know that Navision allocates memory to hold this temp table. Since this table can be rather large, I wonder if it makes sense to delete records from this temp table again if not needed anymore in order to enhance performance? In other words: Does Navision free the space in memory again if the size of a temp table shrinks again?

One would think so - Annother thing to remember, is that the temporary table only have the records sorted by the Primary Key. - That means, that every time you do a TEMPr.SetCurrentKey the key is generated on the fly in memory. We had an instance, where by loading a lot of records into temporary records, we slowed down the system. Our SQL Server had all the records in the cashe anyways. Also - If you have too many records in a TEMP table, you risk that the users PC now has to use the SWAP file - and we do not want that to happen, do we :wink:

Henrik, How many records would be too many in a temporary table?

Hi Dear, What is the maximum records that is expected to be stored in a temp table? What is the basic requirement that you are going to fulfill using temp table? Best Regards, Mukesh Verma

@Henrik: yes, one would think so, but: after all those memory issues with Attain and Win 98… one cannot be sure :-). That’s why I asked if somebody knows for sure. @Chris: How much memory would be to much on a PC? Honestly - this question depends on the power of your client pcs.

Temp tables occupy very little memory. You might say too little. Navision 3.10/3.60/3.70 will allocate up to 300K for each temp table - the allocation will begin when the first INSERT occurs and continue up to that limit as inserts/modifications are made - the remainder of the storage occupied by the temp table is written to disk in a temp file so you will see no significant memory usage for a temp table. None of this space is freed until the table handle is out of scope so deleting records in a temp table does not reduce this memory nor the size of the temp file. In Attain 3.01 this threshold was 30K, from Navision 4.0 it is 500k.

Thanks Robert. That’s the answer I was afraid of :-). Well then my status dialog “Minimizing No. of Records” at least does look impressive… :slight_smile:

The up-side is that you can have almost as many records as you want (<2.6billion) in your temp table without worrying about memory issues inc. swapping - and without a performance problem since they are b-trees.