topic Re: Delete mapping tables to free memory in QlikView

Delete mapping tables to free memory

brunodec — Wed, 13 Jan 2016 22:42:40 GMT

Hello,

Mapping tables are automatically deleted at the end of script execution. However is there a way to delete a mapping table during script. Would that be useful to free memory in case of large mapping tables only used once in the script ?

Re: Delete mapping tables to free memory

robert_mika — Wed, 13 Jan 2016 23:49:42 GMT

You can always drop the table during the scripting execution.

Re: Delete mapping tables to free memory

swuehl — Thu, 14 Jan 2016 00:06:08 GMT

Robert, are you sure about that?

I believe you can only DROP resident tables and I get 'table not found' if I try to DROP mapping tables (and to answer the OPs question, I don't know any other method to delete mapping tables during script run).

Re: Delete mapping tables to free memory

robert_mika — Thu, 14 Jan 2016 00:28:39 GMT

You are right Stefan.

I misunderstood the question.

Bruno please ignore my advice.

Re: Delete mapping tables to free memory

avinashelite — Thu, 14 Jan 2016 04:17:53 GMT

your rite, Mapping tables will be automatically deleted at the end of the script and we don't have the control to delete it in the middle .

Re: Delete mapping tables to free memory

amit_saini — Thu, 14 Jan 2016 04:28:44 GMT

Hi Bruno,

Mapping table gets deleted automatically when you are running the script. If they are still present than could be something wrong with ur script.

Thanks,

Re: Delete mapping tables to free memory

PradeepReddy — Thu, 14 Jan 2016 04:40:34 GMT

Mapping Tables will be deleted automatically at the end of the script execution.

We can't drop/delete the mapping tables in middle of the script execution.

Re: Delete mapping tables to free memory

HirisH_V7 — Thu, 14 Jan 2016 05:28:08 GMT

Hi,

I don't think you need to check, they do get deleted after reload.

If mapping tables were there, your model would be screwed up.

Use a system table to verify.

-Hirish

Re: Delete mapping tables to free memory

NareshGuntur — Thu, 14 Jan 2016 06:10:46 GMT

How big is your mapping table?

Does it contain huge number of rows?

Mapping table will not take much memory and doesn't make any difference even if you are able to delete during the script execution as it contains only 2 fields.

Cheers,

Naresh

Re: Delete mapping tables to free memory

brunodec — Thu, 14 Jan 2016 08:15:40 GMT

Hi Naresh,

Currently I'm building a model in test so impact is small but when going into production I will have about 60 mapping tables with some of them having more than 50k rows, So technically this would mean maximum 3 million rows stored in memory during the whole script run. As of course also the resident tables are in memory I thought it would be good to delete tables from memory - whether it be mapping tables or resident tables. But apparently it is not possible.
If you say mapping tables don't take much memory then this is good news. I felt some compassion with my computer who needs to handle so much data 🙂

Thanks,

Bruno.

Re: Delete mapping tables to free memory

Peter_Cammaert — Thu, 14 Jan 2016 09:44:10 GMT

Hi Bruno,

As others already pointed out, you cannot delete a mapping table by using a statement.

Added to that, mapping tables seem only capable of growing in size. However, they do not autoconcatenate like other tables do. Ok, you might think, let's redefine them with fewer values to reduce their memory footprint. But that won't work either, as a mapping table will act exactly the same as a field symbol table (if you're familiar with the concept): during each successive load of the same mapping table, index values (1st column) that weren't already present in the mapping table will be added, while the others will be left alone.

We do have some luck: a mapping table contains just one entry for every index value. We can apply the field cardinality rule to mapping tables: the fewer cardinal values you store in a mapping table, the smaller the mapping table will be.

In short:

if you don't really need a large mapping table, don't use it.
If possible, reduce the number of distinct values in the index field to reduce the memory footprint.
If all optimisation tricks have been exhausted, resize your QlikView server platform to accomodate your documents (& reloads)

Have a great 2016!

Peter

PS Mapping tables can eat RAM like any other QV internal table. A mapping table with 10.000.000 distinct values will need as much as 1.0GB of RAM. Just to store the lookup table.

Re: Delete mapping tables to free memory

brunodec — Thu, 14 Jan 2016 10:38:28 GMT

Thank you everybody for your feedback. I'm sure now it is not possible 🙂

Perhaps an idea for QlikTech to enable removing mapping tables in a future release.

Re: Delete mapping tables to free memory

shahamel — Thu, 01 Mar 2018 07:47:03 GMT

According to this thread on the Qlik Community (https://community.qlik.com/thread/199468) you can't
delete a mapping table from it's memory before the script has ended.

To make sure youre mapping tables don't concatenate which each loop they encounter, you have to
follow these instructions:

1. Delete the word MAPPING from your mapping table.
2. Store that table under the same name.
3. Drop that table.
4. Create the original MAPPING Table from the data which you just stored
5. Use an applymap statement which uses the values from that above mentioned MAPPING Table

This method makes sure you use 1 MAPPING table with distinct values where no concatenation has
taken place. The example which i provide underneath show what i just explained

With kind regards

S.H.A.Hamelink

1-3-2018 !

// Step 1.
for each begin in 'Adriaan','B','Shoba'

begin:
Replace
// Mapping
LOAD * INLINE [
ABC, NUM
A,$(begin)
B,Bassie
C,3
];

next

for each begin in 'Afas','B','Ofelia'

begin:
Replace
// Mapping
LOAD * INLINE [
ABC, NUM
A,$(begin)
B,Zorro
C,D
];

// Step 2.
store begin into begin.qvd (qvd);

// Step 3.
DROP Table begin;

// Step 4.
begin:
Mapping
LOAD
ABC,
NUM
FROM
begin.qvd (qvd);

// Step 5.
final:
NoConcatenate
LOAD
ABC,
ApplyValue,
ApplyMap('begin',ABC,null()) as NewestApplyVersion;
LOAD * INLINE [
    ABC, ApplyValue
    A, 1
    B, 2
    C, 3
];

Re: Delete mapping tables to free memory

MT — Thu, 21 Mar 2019 02:21:08 GMT

I've run into this as well. To free the memory just reuse the mapping name and load it with a single record. If you watch the performance monitor while its running you'll see the memory released.

Good Luck!

Re: Delete mapping tables to free memory

syedalamdar — Thu, 13 Feb 2020 21:26:26 GMT

There is a hack you can try. Stop the process where you want to drop the Mapping table and output a QVD. Create a new application and reload the QVD and continue the ETL process. You can chain these apps in the task so the first process is completed and then the second process starts.

Apart from having multiple other issues with this solution (disk management and code management in general), I don't really recommend this approach but this was a question I thought should have some solution.