topic link table performance optimization in QlikView

link table performance optimization

ilanbaruch — Mon, 20 Feb 2017 08:39:31 GMT

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.

Re: link table performance optimization

jonathandienst — Mon, 20 Feb 2017 08:58:46 GMT

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.

Re: link table performance optimization

marcus_sommer — Mon, 20 Feb 2017 09:04:07 GMT

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

Re: link table performance optimization

ilanbaruch — Mon, 20 Feb 2017 09:18:54 GMT

hi Jonathan,

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

- load performance.

- front performance is ok.

thank you

Re: link table performance optimization

Mon, 20 Feb 2017 09:44:44 GMT

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