topic Re: Qlik Sense Desktop - Reload suddenly very slow in App Development

Qlik Sense Desktop - Reload suddenly very slow

mohamed_t — Fri, 17 Mar 2023 15:46:12 GMT

Hi everyone,

I did a windows update yesterday on my laptop. Since this update, the reload in Qlik Sense Desktop is suddenly very slow.

Before the update, the time reload was between 1 min 15 and 2 min. Now it's around 5 minutes.

I am using Windows 11 and have 16Go Ram.

My client is QLik Sense Desktop May 2022 Patch 5

Anyone else has already encountered a similar issue please?

Thanks !

Re: Qlik Sense Desktop - Reload suddenly very slow

lmcsedyz — Thu, 18 May 2023 16:31:28 GMT

I have the same problem after update, but on Qlik sense server - apps that were loading 15min in older versions, load hours now.

Issues are loading bigger QVD files with for example 5 statements in where (all of them simple, no killers like "exists")

Re: Qlik Sense Desktop - Reload suddenly very slow

mohamed_t — Mon, 22 May 2023 10:02:03 GMT

Hi @lmcsedyz ,

Thanks for replying.

Did you have any idea or solutions to solve it?

Re: Qlik Sense Desktop - Reload suddenly very slow

lmcsedyz — Mon, 22 May 2023 10:47:00 GMT

Hi @mohamed_t

I tried to remove all "where" conditions to just load, and all operations do after, in resident table - did not help.

I split QVDs to more smaller files - did not help.

I also tried the same with txt files - did not help.

tried combinations - did not help. I am on 16 vcores / 32GB RAM. Before update was everything OK.

I also tried to upgrade to new IR realease may 2023 - did not help.

So, still stuck and this week it is my priority

Re: Qlik Sense Desktop - Reload suddenly very slow

mohamed_t — Mon, 22 May 2023 11:01:25 GMT

Hi @lmcsedyz

Ok can you keep me up to date please?

Your issue appears after Qlik update? (for me, I did not update Qlik)

Re: Qlik Sense Desktop - Reload suddenly very slow

lmcsedyz — Mon, 22 May 2023 11:25:18 GMT

Hi @mohamed_t , I will tel you know.

It could be windows updates, because before Qlik update, it was necessary to do win updates as well.

What is on the to do list: Try to change server/engine configuration and change data source - for example, copy data to amazon S3 storage - will different destination help?

Re: Qlik Sense Desktop - Reload suddenly very slow

marcus_sommer — Mon, 22 May 2023 13:50:14 GMT

In regard to where-clauses quite the opposite is true and exists() is very fast compared with other types of conditions especially if you could use a single exists() with just a single parameter because such load would be loaded optimized (if no further transformation are applied).

Re: Qlik Sense Desktop - Reload suddenly very slow

marcus_sommer — Mon, 22 May 2023 14:02:09 GMT

Before wondering what might happens to the performance make sure that the data-sets on all stages are the same like before. Already very small changes to the loads or the order of load-tasks and/or the data-interpretation could have a big impact. If for example any filter or join is working like expected and resulting in a significantly bigger data-set and which hits now the available RAM and leads to a swapping with the virtual RAM.

Quite helpful to find the cause would be also to compare the document + task logs from before and now as well as to look on the application/event/performance logs from Qlik and the OS.

Re: Qlik Sense Desktop - Reload suddenly very slow

lmcsedyz — Mon, 22 May 2023 14:17:21 GMT

not true @marcus_sommer in our case, if "exists" reaches bigger amount of data (hundreds of thousands already loaded keys), it becomes extremely slow. And if you have suitable amount of memory (so you have good server and you are nor on the cloud), it is much faster to do inner join after this kind of load. Compromise is simple "where" for load (dates are greater than x, rangesum of few fields is bigger than zero, some flag fields are not null etc) and do inner join after load. It depends on, what kind of operations are done by engine during load and which are applied after load (so rows have been loaded, no matter what is in "where", because it will be evaluated after load of QVD). Solution in this case is in separated QVDs for example for each month: DATA_2023_05.QVD with "exists" and "where" as you want, because it will be not memory intensive and much faster, because release of memory is after each QVD loaded. So if you load from [lib:.... DATA_INVOICES_*.qvd] (qvd); you are happy and fast. Big QVD is death for qlik sense... you will be faster with more medium sized QVDs, than a single one

Re: Qlik Sense Desktop - Reload suddenly very slow

marcus_sommer — Mon, 22 May 2023 15:41:51 GMT

My experience in this matter is different. And we need to be careful not to mix here different scenarios, like loading from one big file against several sliced ones and the way how these data are filtered and/or transformed.

Loading from a big file is rather seldom the most suitable approach because not all following reports will need all these data and also any (multi-staged) incremental logic must consider all old data. Common are logic which apply rolling n days/months back. Therefore a slicing of the data into YYYYMM and/or Channel/Company/Country or similar is often more useful. Of course this will add some extra overhead because each single load must be initialized by breaking the load-process. By just a few dozens of files it's not very significant but by thousands of files it would have an impact.

Further that slicing could be done smart by including the relevant information within an appropriate folder/file-structure to filter the data already on the file-level and not on the data-level because there is only one exists(KEY) possible to keep the load optimized. This means applying loads per dirlist() and filelist() loops and within them to control the wanted data-areas with variables and/or if-loops.

In regard to the where-clauses it will surely depend to the data-set and the load-order which kind of filtering would be the most fastest one. And of course the lesser distinct keys needs to checked within an exists() the faster would be the load which means that also the load-order has an impact.

Most important is to keep all bigger loads optimized. It may need some extra steps to create appropriate intermediate qvd's and/or to integrate there appropriate Key's (by cleaning/preparing and/or combining fields) and/or to adjust the order of loads and/or adding some extra loads which extract/store the relevant Key's and/or various renaming/dropping statements for tables/fields. It may sound like a lot of work and it is - by doing it afterwards but if all load-stages are in beforehand appropriately designed it requires not much extra efforts.

From this point of view I would be very surprised if where clauses which compares dates/keys >= <= or filters by inner joins are more performant because these actions will be performed on a row-level while an exists(Key) is performed on a column-level.

Re: Qlik Sense Desktop - Reload suddenly very slow

lmcsedyz — Mon, 22 May 2023 15:53:44 GMT

yes @marcus_sommer , it depends on very specific scenarios. I always do big performance tests before implementation to production (lots of loads for lots of clients and different types of apps / data with different approach - simple statics for one client vs. masive benchmarks of entire country with full deep dive)

But in this case and this issue, all loads are slow, no matter the size (in comparison with previous state before update) 😞

Re: Qlik Sense Desktop - Reload suddenly very slow

marcus_sommer — Tue, 23 May 2023 07:21:24 GMT

I assume that not always the Sense release has changed (really the same kind of installation and/or node-distribution of the services and the same configuration ?) else also the OS release and/or the environment (changed machine, VM, domain, ... and with it possible different matters in regard to the network/storage and/or security settings)? If everything is significantly slower as before it's quite unlikely an issue with the Sense release itself.

Re: Qlik Sense Desktop - Reload suddenly very slow

lmcsedyz — Fri, 26 May 2023 07:51:10 GMT

@mohamed_t I solved it!

What has changed:

before upgrade, qlik loaded QVD and did all operation in load statement, multi threaded if possible.

But after upgrade, are all operations processed during load by single core - load from qvd and operations proces on same thread of CPU and operated during! load of each row.

But operations in in resident tables are OK and multi threaded.

DETAILs:

I did tests - only change is the way, how qlik proces the load. Before upgrade, simple operations on fields or in where statement, where processed multi threaded and fast but much higher memory consumption.

Now almost every extra instruction makes the load of QVD megaslow.

Only working cure: Always! do "vanilla" load of QVD. Desired columns from QVD without any transformation or duplicating, creating new fields - nothing.

Only allowed statement in where is "exists".

Then, do your load from this temporary vanilla table, now all your statements and wheres run multi threaded and fast, then drop vanilla temp table. Hint: exists statement in vanilla load is very fast, but in resident load is slow (inner join much faster)! So if you have bigger source QVDs (GBs), do some kind of preload, to define key for exists statement. Then do vanilla load with "exists", all other stuff in resident load and drop vanilla table when not needed anymore.

Old processing was fast but much more RAM hungry, maybe something detecting memory size is wrong after upgrade (win or qlik or both) and qlik changes how it works to make load less memory intensive.

Re: Qlik Sense Desktop - Reload suddenly very slow

marcus_sommer — Tue, 30 May 2023 07:28:03 GMT

Your observed cause and the workaround you developed would mean a fundamental change in the way how Qlik processed the data-flow - without any necessity and any improvements. Therefore I have serious doubts that there is such general change within the last releases.

More likely seems to me that there is any unsuitable combination of VM + OS + BIOS + Qlik settings in regard to the available hardware. It's quite normal to expect that (on the paper) identically hardware results in equally performance and that more resources would even improve the performance but the opposite could happens if the system couldn't handle all threads properly and previous parallel processes are now sequentially executed.

So IMO the above shouldn't happened and therefore the question from above again - was anything else changed unless the Qlik release within the environment. In my experience each part of a potential upgrade should be done alone which means a tool upgrade and also an OS upgrade should be always made against identically VM respectively hardware. If this worked like expected the underlying hardware might be adjusted. And on top of all a newer business release of the application-chains might be introduced. This means this are four separated steps in a testing-environment and each one carefully checked and yes it sounds like a lot of efforts - and it is - but it means also much lesser risks of issues and afterwards efforts to find and fix the reasons behind them.

Re: Qlik Sense Desktop - Reload suddenly very slow

lmcsedyz — Tue, 30 May 2023 07:49:09 GMT

Server is for years without any change... only windows updates and qlik updates... Only major change was security, but it was months ago before qlik upgrades and without any impact. Other performance indicators, tests and benchmarks are without noticeable change , storage, cpu, ram, cache...

Other operations are fine, Qlik GUI resposnse is good and fast, working with apps has no issues. Just described data load.

Re: Qlik Sense Desktop - Reload suddenly very slow

marcus_sommer — Tue, 30 May 2023 08:09:39 GMT

It excludes some possibilities. If I think again about your description it means accessing the RAM is fast and accessing the storage/network is slow and the last may now look like a single-threaded operation but this impression may cheat and the system would assign more threads if there were more data at the time.

So it might be helpful to try various I/O testings against the storage/network - not only with Qlik else also with other tools (for example notepad++ with opening larger csv and storing them) and Windows just by copy & paste tasks. Maybe there are now any security measures in action. Another thought goes in a new/changed way of encryption in regard to access the storage/network.

Re: Qlik Sense Desktop - Reload suddenly very slow

lmcsedyz — Tue, 30 May 2023 08:49:10 GMT

I did... a lot of testing, because first what I was thinking about, it was storage, BUS, network and cache issues.

for latency, speed, processing files with different size etc...

But I will not work on this server much more longer. We are OEM - so it is Qlik for our clients and we will migrate at the end of the year to Qlik Cloud (multi-tenant SaaS for OEM). So I am happy it is able to run few more months, I am also testing how this scenarios, works on cloud... A lot has changed since 2020... In few cases change of approach has impact on performance even on the cloud.

At the end of the day - everything bad could do some good

Re: Qlik Sense Desktop - Reload suddenly very slow

NadiaB — Fri, 23 Jun 2023 14:13:17 GMT

Hi @mohamed_t

Perhaps a new process or antivirus is scanning things ? You could use Process monitor pointing to your QVD's path and Qlik Sense Desktop installation folder https://community.qlik.com/t5/Official-Support-Articles/File-Access-and-Process-Monitoring-How-to-find-locked-files-and/ta-p/1712539

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Sense-Folder-And-Files-To-Exclude-From-Anti-Virus-Scanning/ta-p/1715685

Hope it helps!