Big table - qlik desktop crashes PC - CPU usage 100 %

[Groflo]

Hi,

I'm a new user of Qlik and it seems that it suits perfectly my needs !

But I experience a bug really anoying that prevents me from using Qlik desktop : I loaded a big fact table (7 millions rows) with 3 small dimensions and 2 quite big (time and geography), from postgresql.

When I tested with a filtered database (loaded it with filter to have only few rows, like 20k or so), it worked nice and I created the app i wanted.

When I tried to do the same with the complete database (new app created), it loads, but quickly after that, when I edit sheets or else in app, Qlik starts using 100 % of my CPU and RAM and freezes PC so much I can't even move my mouse and kill the software : I have to reboot my PC.

It seems that it happens to some other users (see that post), but it's an old post. Is there any solution now about this ? Frankly, with that bug, Qlik is useless for me, as I plan to work on several databases of that type…

Thanks in advance !

[Oleg_Troyansky]

HI,

A table with 7 mln rows shouldn't bring down your desktop, if you have a decent amount of RAM (8GB or even better 16GB). It looks like you are able to load the data and save the document just fine.

The fact that the application is crashing in the run time tells me that you might be using some heavy calculations in the layout. I'll give you top three reasons for such behavior:

1. Using tables that are not linked (associated) - in this case, QlikView creates a Cartesian Join between your fact table and a dimensional table if they are not associated in the dataset.

2. Using IF() conditions within your aggregation functions - like SUM(IF(....)). This is a very heavy operation that can crash your computer on a relatively large dataset.

3. Using AGGR() functions with extremely detailed dimensions - that requires a lot of RAM and processing time and can easily crash your system.

SO, if you are planning to use QlikView for analyzing millions of rows, you need two things:

1. Adequate hardware, most importantly - enough RAM. Analyzing millions of rows without enough RAM is impossible

2. Basic understanding of Qlik performance best practices - what's good and what's bad for Qlik performance.

Cheers,

Oleg Troyansky

Check out my book QlikView Your Business for deep hands-on tutorial on QlikView development, including good performance habits.

[Groflo]

Thanks a lot for your response.

I have only 8 Go RAM, and I know that's the minimum, but my crash does not come from functions like If ou Aggr, as it happens even with empty sheets. For example, it happens if, by mistake, I click on « selections » or « Informations » (see attached pictures, Qlik is in French, I don't know if it's the same in english version).

---

@Oleg_Troyansky wrote:

1. Using tables that are not linked (associated) - in this case, QlikView creates a Cartesian Join between your fact table and a dimensional table if they are not associated in the dataset.

---

When I import data, first thing I do is to create joins between my fact table and dimensions, of course.

[edit : don't know if it matters, but I made a mistake as I posted in QlikView forum instead of QlikSense desktop, which is the software I use]

[Oleg_Troyansky]

Hi,

When it comes to performance, QlikView and Qlik Sense behave quite similarly, even though QlikView doesn't have tools like "Selections" or "Insights" that seem to be crashing on your machine.

Both of these tools are quite heavy. The "Selections" tool needs to build filters with all your fields and fill them in with all distinct values that your fields have. That could be quite heavy. The "Insights" tool needs to analyze your data in order to offer some automatically generated insights. It is possible that with your data and with your (minimal) hardware, these tools could be too "expensive" from the performance standpoint.

It's quite difficult to diagnose the issue by guessing what the problem might be, but it is unlikely that loading 7 mln rows of data, while following good  habits of performance, would do it.

I can recommend that you analyze your document using the QS Document Analyzer tool:

www.qlikviewcookbook.com

Pay attention to fields that are not in use and especially to fields that use a lot of memory. Once you identify those, you might be able to optimize your App for the amount of hardware that you have.

[Groflo]

thanks for your answer.

I do realize that I can easily work and do the job if I don't use tools like selections nor use the automatic chart, which does the same.

With the same database but limited to 10% of the rows, I don't have any problem.

I guess I just have to be careful not to click on those fields 🙂

[Brett_Bleess]

Here is a Design Blog post that may shed some further light upon things, potentially, feel free to search around in that entire area, there is a lot of great information there and lots of examples etc.  Oleg's advice is great as well, he has been doing this stuff as long as I have, but he is a lot better at the development stuff than am I! 🙂  Hopefully the following link may prove useful, but again, if you back up one level, you can search across the entire Design Blog site, and there are hundreds of posts in there.

https://community.qlik.com/t5/Qlik-Design-Blog/Data-Modelling-Clarity-vs-Speed/ba-p/1473644

Regards,
Brett