topic Re: Performance issue in QlikView

Performance issue

udaya_kumar — Wed, 24 Aug 2016 14:23:16 GMT

Hi,

I have the fact table with 252 million rows and it is joined with other dimension tables.

I have a combo chart with product and month as the dimension and count(distinct id) as the expression.

I have a listbox with month and product selection. If i select few months in a listbox, then the chart takes nearly 40 - 60 secs to calculate the count. And when i check the value in Calctime of chart in sheet properties, it gives the value in millisecs as 41340 or 60432.

How to improve the performance of the chart?

Re: Performance issue

marcus_sommer — Wed, 24 Aug 2016 14:28:42 GMT

How looked your datamodel?

- Marcus

Re: Performance issue

udaya_kumar — Wed, 24 Aug 2016 14:41:54 GMT

It has two big fact table with one table with 251 million rows and another with 175 million rows, 4 dimension tables and 2 dimension tables joined to other dimension tables. Those fact tables are linked with two ids id1 and id2, so it has created one synthetic key bw two fact tables.

Re: Performance issue

vishsaggi — Wed, 24 Aug 2016 15:13:33 GMT

Can you send us a screen shot of your data model. It is most of the time good to avoid synthetic keys as they give incorrect results unless it is necessary to have them.

Re: Performance issue

marcus_sommer — Wed, 24 Aug 2016 16:29:26 GMT

With a synthetic key between such large fatc-tables it's no surprise that the response-times are slow. You need to remove them and quite probably you will need more changes and trying to merge both fact-tables to get a fast gui.

- Marcus

Re: Performance issue

satishkurra — Wed, 24 Aug 2016 16:48:11 GMT

I agree with Marcus Sommer.

If you have 2 fact tables. As per data modelling principle, we should never associate 2 facts directly and moreover , if synthetic keys exists in the model, the performance will be still poor.

See if the below link helps to resolve the data model issue.

Concatenate vs Link Table

https://community.qlikview.com/thread/89056

If you still want 2 fact tables, i would recommend to associate the dimension tables to the lowest grain level fact and not the other fact with high grain level...

Re: Performance issue

udaya_kumar — Thu, 25 Aug 2016 06:16:08 GMT

But the fields i am using in the chart are from a single fact table.

The dimensions and count(distinct id) are all from a single fact table, so will it still affect the performance of the dashboard because of the synthetic key bw two fact tables, even if i use the fields from a single fact table.

Re: Performance issue

marcus_sommer — Thu, 25 Aug 2016 07:01:49 GMT

If really all fields comes from one table a change from the table-associations might not effect the calculation-times but I would at first eliminate all obviously weaknesses of the datamodel before going further. If you are on this step then take a look here: Symbol Tables and Bit-Stuffed Pointers - a deeper look behind the scenes.

- Marcus

Re: Performance issue

Anonymous — Thu, 25 Aug 2016 07:05:59 GMT

Hi Uday,

Below are few best practices you can follow to improve the performance of your Dashboard

- Using set analysis instead of if-else conditions

-Use a link table to join all the Fact tables (Star Schema). This will create a single fact table and reduce the number of synthetic keys.

-Use less number of variables which consume CPU time

-Use the flags for the filters which are used in the expression. The simpler the expression the faster the chart is rendered

-Move all complex calculations to the script level and minimal at object level

-Avoid RESIDENT loads. If RESIDENT has to be used then there are 2 ways to work on it so that the performance is not affected.1. Use no concatenation and 2. use DROP table.

-Use built-in functions instead of macros

Hope this helps.

Regards,

Laxmi

Re: Performance issue

marcus_sommer — Thu, 25 Aug 2016 07:37:50 GMT

One addition: with count(distinct id) you are not calculating within one table then id is one of your key-fields and part from the synthetic key (and in general don't count a key-field).

- Marcus

Re: Performance issue

udaya_kumar — Thu, 25 Aug 2016 09:22:05 GMT

Yes, there are two fields joined bw two fact tables, out of those one field is a key field in the data model.

I am using that key field to count the distinct id's based on month and the product wise.