topic Re: Large App with Section Access is SLOW to open in App Development

Large App with Section Access is SLOW to open

Duber101 — Thu, 21 Nov 2024 21:53:21 GMT

The data size is big, the App is large, and the App opening time is SLOW. Section Access ensures performance navigating sheets is adequate, but the App opening time of 5 mins isn't ideal. What are the options ?

To provide some background, the source data is approaching 300 million transactions, and the transactions have been entered by approx. 1,000 different entities. Each entity needs to see their own data and no-one else's so an App has been created with Section Access. The App is approximately 10 GB in size. When an entity opens the App in takes approx. 5 mins to open, but after Section Access kicks in and reduces the data down to a single entity, the App performs adequately displaying visualisations in seconds. So the issue is solely the time it takes to open the App. Is there a way to improve the performance of opening the App for each entity ?

Please note :

1. the data model has been extensively tuned and trimmed e.g. it doesn't contain any redundant data or superfluous fields, auto numbers are in use, the model is optimised for Section Access etc.

2. the data model requires unit level data and can NOT be aggregated in the data model

3. smaller Apps have been created for tailored purposes, but a 'wide' and 'deep' App ('the App' discussed here) is required

4. the App takes a number of minutes to LOAD when restricted to a single entity, so on demand App generation isn't a viable option

5. the infrastructure has been optimised within reason

From what I understand, cache warming isn't an option since it doesn't really work with Section Access, since it would need to effectively replicate App openings by the 1,000 different entities, which isn't viable.

At this stage, the only viable option appears to be to split the App into multiple Apps, where users are divided into groups and given access to the App containing the subset of data relevant to them. This is obviously messy and increases maintenance.

Are there any other options ? Please help.

And more generally speaking, how compatible is Qlik Sense and large data ?

Re: Large App with Section Access is SLOW to open

marksouzacosta — Wed, 12 Jun 2024 03:44:38 GMT

Hi @Duber101,

I see you have covered most of the common and advanced topics to increase app performance. Well done. Splitting the app in multiple ones may be the only viable solution.

In any case, I have two suggestions:

1 - QSDA Pro may help you to increase the overall performance of your app. This is a fantastic tool. A must have. Please take a look here for more details: https://easyqlik.com/
@rwunderlich is your contact if you have any QSDA Pro questions.

2 - I think you are using Qlik Sense on-prem right? If so, have you tried the new pre-load tasks? Not sure if this is the same thing you are talking about cache warming. More details: https://help.qlik.com/en-US/sense-admin/May2024/Subsystems/DeployAdministerQSE/Content/Sense_DeployAdminister/QSEoW/Administer_QSEoW/Managing_QSEoW/create-preload-tasks-from-tasks.htm

Re: Large App with Section Access is SLOW to open

Duber101 — Wed, 12 Jun 2024 07:48:32 GMT

Hi @marksouzacosta,

Thanks for the suggestions. Yes on-prem. Quite a bit of time has been spent tuning the App, so whilst I can see that QSDAPro is a great tool, I suspect it's not going to reveal too much I haven't already implemented or considered. In relation to preloading ("cache warming") I believe the limitation with it and how Section Access works, is that preloading the App doesn't reduce the time to open the App for users restricted by Section Access. I am yet to test this but anecdotal evidence supports it in my experience, and it's also supported by what I've read. For mind, since the Section Access data reduction loads all data into memory as a first step, it would be great if this particular part could be cached so that all section access users benefit from either; the first user who opens the App, or preloading. Alternatively, it would be better if only a portion of the data was loaded into memory when a section access user opens the App. I'm no doubt overlooking numerous technical reasons for why Qlik Sense doesn't work this way.

Re: Large App with Section Access is SLOW to open

marcus_sommer — Wed, 12 Jun 2024 12:48:12 GMT

I never used the pre-load feature and don't know how it's implemented within Sense - but AFAIK it should be helpful to reduce the opening time of an application. Regardless if there exists a section access or not. Because it loads the (compressed) application from the storage/network into the RAM. Afterwards each access happens against the RAM and therefore it should have benefits.

Beside this I suggest to review the data-model and the way how the section access is implemented. Especially larger data-sets should be used in star-scheme data-models (and sometimes even in a big table). You may also try if a link of the section access is more performant against the fact- or a dimension-table and also not listing all single users per entity else user-groups.

Further you may play with enabling/disabling session recovery, shared-files, removing all UI objects (just one empty sheet), disabling all actions and similar stuff if it has an impact. Also helpful would be to remove the section access to see the difference between them - if it takes 4:50 instead of 5 minutes it would mean that the bottleneck isn't the section access - I think I would start with this measurement.

Re: Large App with Section Access is SLOW to open

rwunderlich — Wed, 12 Jun 2024 13:44:20 GMT

If I understand correctly, the 5 minute time-to-display is incurred for each user, not just the first user to open the app, correct?

You can check time spent in the open process steps using the excellent free tool Qlik Explorer for Developers. QSDA Pro provides this open progress data as well. Of particular interest in your case may be the user access steps highlighted in the QED image below.

The delay may also be due to initial chart calculation which you can diagnose as @marcus_sommer suggested by making an empty sheet. If your issue is in charts, QSDA Pro can help you identify the problem charts and suggest tunings.

Let us know what you discover.

-Rob

Re: Large App with Section Access is SLOW to open

Duber101 — Thu, 13 Jun 2024 01:17:28 GMT

Hi @marcus_sommer,

From what I've just read, unlike QlikView, there is no Session Recovery functionality in Qlik Sense. I'll look into the shared-files, but I'm pretty sure the App is not using file-share. Loading the App with no sheets is an interesting idea for troubleshooting, I'll try that.

The data model is a star-scheme (fact and dimension), and section access is tied to the fact. Each user is from a different entity and requires a different view of the data so user-groups aren't useful unfortunately.

Section access itself is not affecting how long it takes to open the App. The App takes roughly the same time to open when section access is removed. The App is slow to open due to it's size (10GB) which needs to be loaded into memory. Cache warming can overcome this issue for large Apps however I thought it was ineffective for section access users. My suspicion is that this would also be the case for pre-load, but I may be wrong, and it would be great if your correct and it does have benefits for each user with a different view of the data through section access.

If any-one has experience with cache warming / pre-load and if it's effectiveness for section access users it would be great to hear from you.

Re: Large App with Section Access is SLOW to open

Duber101 — Thu, 13 Jun 2024 05:12:19 GMT

Hi @rwunderlich,

Thanks for your thoughts.

Yes anecdotal evidence suggests that each user restricted under section access, is incurring the 5 min time to open, rather than just the first time user. I will test this shortly to firm up.

In relation to the the impact of the initial chart calculations, the same App with all master measures/dimensions and sheets removed does appear to open a little faster, but it's only about 5-10% faster so tuning the front-end is only going to result in a marginal gain at best.

Do you know whether the new pre-load feature caches differently to some-one manually opening an App ? If it does that would solve my problem, and likewise benefit every-one with App's containing section access and multiple different users.

Re: Large App with Section Access is SLOW to open

marcus_sommer — Thu, 13 Jun 2024 07:24:00 GMT

To conclude: section access as well as the UI objects have no significant impact on the opening times - just a few percent, right? This would mean that the application-size and the available network/storage performance are the essential points.

If not a local ssd-storage is used else any kind of network this connection would become a bottleneck by larger data-sets. And by a 1 gb/s connection the pure data-transfer of around 10 GB would need approximately 1:30 minutes - in the best case - only this application is using the connection at this time and no other load-balancer/proxy routing and/or firewall/group policy or similar stuff is impacting the traffic.

Without diving deeper in all such network-stuff you may try a direct copy & paste from the source to the target and/or opening the app with the desktop client directly from a local ssd. If available/possible both measurements are rather quickly to check and would give a better "feeling" what to do as next. If the direct copy & paste also needs about 5 minutes you would have directly identified the bottleneck.

Beside this a pre-loading / cache warming should have an impact. Like above mentioned I never used this kind of feature explicitly but I could confirm that opening a larger application is significantly faster if any other user has this application opened before and the application is available within the RAM. If each user has an opening time of 5 minutes although the application is already within the RAM it means that the application is transferred multiple times in the RAM without sharing the cache. In QlikView there is a qmc-setting of: "Allow Only One Copy of Document in Memory" - I think in Sense are similar configurations available.

Another thought might go to the data-set itself. 10 GB for 300 M of records isn't extremely large but there might be a significantly potential to reduce the app-size especially by:

reducing the number of distinct field-values (for example splitting timestamps into dates and times)
avoiding of row-level formatting (a single format for a date-field is stored ones in the header - if there are more formatting each single value is stored with the format-information)
using numbers instead of strings (for example: Date * 100000 + EntityID instead of something like: Date & '|' & EntityID as Key)

Of course there are further possible measurements but the above listed ones could have the biggest impact - if applicable.

You may further consider to adjust the granularity of your data within the application by using mixed ones which may end with an atomic level on the current & last month + monthly aggregated ones for the last 2 years and the older periods are aggregated on a yearly level. Here some background to the idea:

Fact Table with Mixed Granularity - Qlik Community - 1468238

Re: Large App with Section Access is SLOW to open

Duber101 — Thu, 13 Jun 2024 08:09:41 GMT

Hi @marcus_sommer ,

Yes, the section access as well as the UI objects have no significant impact on the opening times - just a few percent. I love the idea of testing for network bottlenecks using a local copy of the App. If a local copy is fast then it's quite telling. Thanks. I'll test shortly whether a user restricted through section access benefit from caching created by other users.

Re: Large App with Section Access is SLOW to open

rwunderlich — Thu, 13 Jun 2024 16:16:56 GMT

If you use the QDE tool I referenced, you will see the IO load time in the :"Loading from storage" step.

-Rob

Re: Large App with Section Access is SLOW to open

Duber101 — Fri, 14 Jun 2024 02:20:17 GMT

@marcus_sommer , @rwunderlich

Testing has proven that section access users do NOT benefit from each other in relation to caching. Tests in sequential order and results were as follows :

User 1 - opens report - 5 mins

User 1 - opens report again - 10 seconds

User 2 - opens report - 5 mins

User 2 - opens report again - 10 seconds

In summary, user 2 did not benefit from the cache created by User 1.

User 1 and User 2 are restricted differently via section access.

It would be great if the load of 'all' data into memory - before section access kicks in - was cached and useable by all section access users, but unfortunately it doesn't work that way. I look forward to re-testing once I have the pre-load feature set-up to check if it caches differently.

If anyone has experience with the pre-load feature and how it caches for different section access users, I would love to hear your thoughts.

Re: Large App with Section Access is SLOW to open

rwunderlich — Fri, 14 Jun 2024 02:27:08 GMT

I recommend you use the QDE tool to determine more specifically what is going on during the open. It's a free tool and is easy to set up.

-Rob

Re: Large App with Section Access is SLOW to open

marcus_sommer — Fri, 14 Jun 2024 05:05:29 GMT

Could you repeat this test - with a removed section access - to see if there happens in general a cache-sharing between the users?

Re: Large App with Section Access is SLOW to open

Duber101 — Fri, 14 Jun 2024 05:29:57 GMT

With no section access,.

User 1 - opens app - 5 mins

User 1 - opens app again - 10 secs

User 2 - opens app - 10 secs

So yeah, the benefits of caching work fine for non section access users (as expected) but not for section access users.

Re: Large App with Section Access is SLOW to open

Duber101 — Fri, 14 Jun 2024 06:15:27 GMT

@marcus_sommer

Based on these findings, do you still think it's possible that the Preload App functionality (May 2024 version) will create a cache that all section access users benefit from ?

Re: Large App with Section Access is SLOW to open

marcus_sommer — Fri, 14 Jun 2024 06:57:58 GMT

Sharing applications between users and using a common cache between them is a main-feature of the in-memory technology from Qlik. But I don't know if this is also the target-scenario by using section access respectively if it has some restrictions and/or may configurable in some way?

From a pure technically point of view it should be also possible with a section access because a section access is nearly the same as an UI selection - whereby a selection flags the parts of the data-set with TRUE/FALSE and the "classical" section access dropped the FALSE ones from the memory. Such data-removing might be replaced with a non-changeable LOCK against the reduction-fields.

So far it looked rather simple but it becomes more complex if an application might be refreshed and/or any API calls were possible or ... These things are surely controllable with appropriate configurations but then the security is depending on a proper configuration and that may a too weak aspect in regard to the risks and costs.

Sonja_Bauernfeind: please involve the R&D team to clarify the intended behaviour and the related configurations.

Re: Large App with Section Access is SLOW to open

Duber101 — Fri, 14 Jun 2024 07:38:51 GMT

Thanks Marcus. Looking forward to hearing more about the intended behaviour and if it's configurable. If it is, that would be brilliant and solve my problem.

Re: Large App with Section Access is SLOW to open

jpletscher — Thu, 30 Apr 2026 15:36:42 GMT

@Duber101 , @marcus_sommer , @rwunderlich

Thank you for this conversation. We're experiencing the same thing. Have there been any findings or recommendations developed on this topic in the nearly 2 years since it was posted? Some closing thoughts would be helpful.

Thanks

-Jim

Re: Large App with Section Access is SLOW to open

marcus_sommer — Mon, 04 May 2026 07:47:01 GMT

If an app is well-optimized and too slow in regard to the expectations there is the general option to increase the hardware-sizing. Some parts are scaling quite well but others not and by special requirements the invests may easily grow exponentially instead of linear.

If more hardware isn't a practically option the app might be sliced into n specialized ones. An all-inclusive app seems often to be the simplest way to combine wide requirements on views even against parallel data-models but it must have also disadvantages beside the pure sizing, for example the section-access overhead by opening an app. There is no wrong/right approach else the front-end/back-end resources/efforts needs to be balanced to the available hardware/manpower.

Beside the above it might be helpful to reduce the included app-information to the necessary ones by using different granularities within the data, like hinted here: Fact Table with Mixed Granularity - Qlik Community - 1468238.