Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More

Performance and Optimization Best Practices in QlikView & Qlik Sense

Employee
Employee

Performance and Optimization Best Practices in QlikView & Qlik Sense

To help Qlik customers and Qlik partners, I amalgamated a group of resources from many contributors over the years to deliver a solid 1 hour performance and best practices webinar.  The presentation was recorded and presented during the AMERICAS Virtual User Group in January 2016.  The feedback has been very positive and I would encourage any individual in a presales , consulting or general  qlik developer to have a look and disseminate how they see fit.

To me , the value of this webinar is as follows

  1. It instills an understanding of why a best practice is a best practice.   If you understand why something is good for performance, you are more likely to come up with ideas to resolve issues.  The webinar establishes a core understanding of how Qlik stores and manages data . This is important because most of our best practices related directly back to the data storage model.  A dimensional field with millions of unique values will create a disproportionately large symbol table that itself contains many disproportionately large bit stuffed pointer values which must also be referenced in the relevant data table ...  that type of thing.
    • Qlik Storage Model and best practices

  2. It helps you understand why hardware is important and why certain hardware introduces latency for the in-memory engine.

    • Data in Motion

  3. Reviews 3 approaches to data volume scalability and the trade-offs , constraints, and benefits of each .  

    • Data Segmentation
    • Direct Discovery
    • On-Demand App Generation
Attachments
Comments
datanibbler
Esteemed Contributor

Hi Jonny,

I read this with great interest. Optimizing existing scripts is my favorite fun way of thinking through the code and

GUI since you have to understand them to be able to optimize.

I think your hint to remove LinkTables from very large datamodels is very interesting - do you mean large in

the sense of "a lot of data" or "a lot of different tables"?

The first would certainly be true for ours - there is an awful lot of data in our DataModels, making the

reports quite slow. I also think the LinkTables are not the best way to solve the issue even if that approach

is apparently being preached by Qlik_consultants up and down the country, along with that (very sensible) system

of directories and relative paths between them and all ...

.

However, the alternative of "table concatenation" leaves me a bit clueless ... could you explain

that concept to me?

I have lots of other questions on that, too:

- What are the concepts of "partial loading" (different from incremental loading) and "parallel loading"

  (parallel in sequentially executed scripts)?

Thanks a lot!

Best regards,

DataNibbler

0 Likes
datanibbler
Esteemed Contributor

About synthetic keys - one hears different things about those, many people say they are not a problem in themselves - I have a datamodel for instance with a few very small separate snowflaked tables. I would like to just join or map them into the linktable - but the fields from three formerly_separate tables go into the SA, so when I join or map them into the linktable, that necessarily results in a synthetic key ...

0 Likes
Employee
Employee

I prefer to eliminate the synthetic key tables only because another developer will see the synthetic table and wonder if the original developer knew exactly what they were doing.  If you are explicit and manually create a link table built purposely with concatenations sourced from both source tables than you also are showing that your intentions were explicit.   At QLIK there are plenty of employees that see a synthetic table and automatically think its a problem and even if it isn't from a performance standpoint ,   i still think its an issue from a code practice.  That is my personal view because i can see this up for interpretation too.

Table concatenation can eliminate a link table in some circumstances by merging two fact tables together as 1 big union.  The commonly named fields align in the resulting larger but sparser table providing associative context.  Its not quite the same thing from a data modeling perspective.  THere are some links out there that explain the difference and there is some proof that one larger table can perform better than 2 larger fact tables linked together with a link table.   Here is a refernce:   A Myth about the Number of Hops.   Its a good read to understand implications of snowflaking too.

datanibbler
Esteemed Contributor

Thanks Jonathan!

I will definitely read this. It's always better to know more and it also has an effect on that hint of yours about straight_tables on the GUI encompassing fields from several tables ...

I'm just reading another book on QlikView anyway. There, like in almost any book, they also state that the Qlik_team in a company should encompass a number of roles/ persons - well, I guess that has not yet been understood in Germany. Consulting companies apart, I haven't yet seen a company with more than one or two persons for Qlik - and there are precious few having as many ...

As for the other hints you give for optimization, I'll follow them up whenever I can. We have a lot of concatenated keys in our DataModels, I'll check whether I can replace these using the Autonumber() function. That should make the whole report a bit more performant on the GUI_layer.

Best regards,

DataNibbler

0 Likes
datanibbler
Esteemed Contributor

Hi,

apparently I had read this before, but it's never a waste of time to revisit this kind of stuff.

So large intermediate tables do have an impact on performance rather than the pure nr. of hops - that would mean large LinkTables also ... well, that's certainly worth thinking about since in our FI application which, generally speaking, reflects the general ledger, that is of course huge ...

0 Likes
Chanty4u
Esteemed Contributor III

awesome   very useful

0 Likes
fscastro
New Contributor

Hi Jonny! You is "The Guy" tks for this awesome post! But, I don't see any best pratices for S.O in this ppt, can you talk a little about this? We have some best pratices about S.O?

I have some doubts about QlikSense, I'm still a bit noob in this world, so I would like help with these questions.

1) Do we have any best pratices document for QlikView / QlikSense S.O (Windows)? I did not find anything like it;

2) My cluster has 250Gb and 20 CPU each machine (We have four), every time the system consumes only about 30% of this (about 80GB), however the OS is paging 50GBs which for me personally is absurd, this it's normal? So I asked for the S.O document to validate.

3) Do we have any best hardware practices for QlikView and QlikSense(I see this in the PPT, but we have some for 2019, because we have a new family of Processors here)?

4) My biggest problem today is that all the data seems to load fast the first time, but when I apply filters this gets very slow and it takes several minutes, slow filters, does anyone have any ideas?

0 Likes
marc8891
New Contributor

Hello Jonny,

My quick question is what is meant by "Table saturation" on slide 6 bullet point 2?

0 Likes
Ezir
Contributor II

Very useful!

Thanks a lot!

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2016-11-08 10:35 AM
Updated by: