topic Re: Improve Performance in Straight Table in App Development

Improve Performance in Straight Table

santho_ak — Sat, 16 Nov 2024 18:19:19 GMT

Although Qliksense is more of visualization tool over a reporting tool. Need recommendation or best practice for the below scenario:

Sheet: I need to create reports which involve roughly around 50 to 70 columns in a table chart object. (https://support.qlik.com/articles/000054308)

Filters within the sheet: Around 10 to 12 filters within the same sheet to filter those columns within the table chart object.

Data volume: Always between 30 M to 100 M records in general

Data Modelling: One layer specific to that report (QVDFile1) as Extract layer. Second layer joined by combining multiple QVD's (QVDfile1 joined with QVDFile2, QVDFile3 upto QVDFile-n) to get data for a single report as Data Load layer.

Need some suggestion and best practice for this scenario on performance wise.

PS: I do use some variables. Mostly they are within the script level. But some variables are unavoidable I have to create only within the GUI. So not sure what is causing the report to slow when filters are applied.

Thanks in advance

Re: Improve Performance in Straight Table

ArnadoSandoval — Fri, 24 Jul 2020 08:14:07 GMT

Hi @santho_ak

This is what I understood from your post:

You have a report hosted by straight table in the QlikSense UI.
Your reports seems to have two layers, the main one based on a QVD (QVDFile1) and a second layer formed by joining a variety of tables-QVDs.
It seems your data load scripts performs a lot of joins involving several tables preparing the second layer of your report.

Would you please include the screenshot of your application Data Model?

Regards,

Re: Improve Performance in Straight Table

santho_ak — Fri, 24 Jul 2020 14:42:12 GMT

Thanks for the response @ArnadoSandoval . Yes your understanding is correct. Attached data model (skeleton) snapshot. Please let me know if this helps

Thanks,

Santhosh

Re: Improve Performance in Straight Table

ArnadoSandoval — Mon, 27 Jul 2020 09:00:39 GMT

Hi @santho_ak

There are lots of articles addressing Qlik tuning and performance in both, script and UI; based in your Data Model these are some of my observations:

Your DM shows two and three level branches; I do not know how many fields (columns) you need from these tables, but the guidelines suggest to flatten them by using AppyMap().
If you cannot flatten those branches, make sure their columns associations are based on numeric columns; the suggestion here is to read about AutoNumber().
You are handling 9 tables; the performance's suggestion is to load the fields (columns) required by the reports, and avoid as much as you can timestamp columns. You should remove unused fields.
There are lots more suggestions.

I will refer you to this one hour video by Jonny Poole Qlik Best Practices: Performance and Optimization; he certainly cover many areas for performance tuning in his video; I also attached the video's power point presentation.

Some notes:

Hope this helps,