Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
ilanbaruch
Contributor III

link table performance optimization

hi all,

a lot has been written in this community on differences between  link table to concatenation to solve several fact tables in single model.

in my scenario i have 2 fact tables with same field names concatenated  into one. all is optimized and working well.

when adding budget table (dim1, dim2, dim3, year, month, amount ) with a link table, budget table is very small and loading fast,

but when loading fact table (resident load) into link table it hurt performance dramatically.


any ideas on how to optimize this load, or another way to load budget into the model would be appreciated.


advanced thanks.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: link table performance optimization

Your performance issue while creating the link-table could be caused by a lack of available RAM - maybe you could change your load-order in some way for example by storing your fact-table into a qvd and then dropping the table and loading the link-table from the qvd - it should save some of the needed memory-space.

Beside them it might be an alternatively if you concatenate your budget-data to your facts - no need to create a huge link-table and the performance within the UI will probably also be better.

- Marcus

4 Replies
MVP
MVP

Re: link table performance optimization

Do you have two fact tables already and you are adding a budget table? Or is the budget the second fact table? Are you referring to load performance or front end performance?

My experience is that link tables can perform poorly in the front end if the data set is large and the link table's cardinality is high, so I would try to combine the fact tables into a single fact table with a source or type field to identify the different records.

If the problem is reload performance, then you could consider an incremental load process to reduce the reload time.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP & Luminary
MVP & Luminary

Re: link table performance optimization

Your performance issue while creating the link-table could be caused by a lack of available RAM - maybe you could change your load-order in some way for example by storing your fact-table into a qvd and then dropping the table and loading the link-table from the qvd - it should save some of the needed memory-space.

Beside them it might be an alternatively if you concatenate your budget-data to your facts - no need to create a huge link-table and the performance within the UI will probably also be better.

- Marcus

ilanbaruch
Contributor III

Re: link table performance optimization

hi Jonathan,

- two already (optimized) and the budget is the 3rd

- load performance.

- front performance is ok.

thank you

Not applicable

Re: link table performance optimization

Hi Ilan,

You mentioned that you are loading fact into link table, if you have fact table separately and dimension table separately there is no need of loading fact into link.

According to me, you are taking dimension fields from Fact and concatenating those values into link table.

Is my understanding correct?

Also, Qlik stores temporary tables while concatenating statement are queued.

For example,

fact1 concatenate with fact2 will create a temp table1,

further concatenating fact3 will create a temp table2

which is concatenation of temp table 1 and fact3,

it continues until last concatenate statement is found.


After that it drops the temp tables and keep only the fact table.It takes huge memory to process this.

So the load performance is affected here.


Regards,

Vivek