Large App with Section Access is SLOW to open

[davidsimpf]

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, so entities 0-333 access App A, entities 334-666 access App B, and entities 667-1000 access App C. This is obviously messy and increases maintenance.

Are there any other options ? Please help.

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

[marksouzacosta]

Hi @davidsimpf,

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_DeployA...

[davidsimpf]

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.

[marcus_sommer]

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.

[rwunderlich]

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

[davidsimpf]

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.

[davidsimpf]

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.

[marcus_sommer]

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 

[davidsimpf]

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.

[rwunderlich]

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

-Rob