QlikView working slow with section application

[papanovn]

Dear all.

I have a problem with one qvw file with section application in it. File size is not so big - it's 800 MB. Data model is not  simple, there is a link table between one master table the calendar and other transaction tables and fact tables. Generally there is no issue with the speed of work when there is no section application in the file. When section application is enabled changing sheets in the file goes extremely slow. The sheet opens when you click on it but then qv hangs and you have to wait from 5 to 10 minutes to be able to do anything in this sheet. This happens even there are not visualized objects in the sheet (every object is minimized). At this moment I've tried to optimize the data model - AutoNumber string keys between tables, drop some satellite tables. Things got even worse. File start to open extremely slow. It took from 20 to 30 minutes to open it in qv.exe. There is enough free RAM on the machine, CPU is not overloaded, so the reason is not hardware. I've even tried to work with this file on different machines to be sure that there is no software problem with the machine. Yes, there are a lot of complex expressions in some of the objects but why qv hangs when all the objects are minimized? And why it's working fast when section application is disabled.

Here are the section access and section application tables.

SECTION Access;
LOAD ACCESS
,NTNAME
,PROFILE
,STOREID
,DEPARTMENTNO
From [..\..\qvwdata\system\Section access.xlsx]
(ooxml, embedded labels, table is [Section access])
;

SECTION Application;
LOAD * Inline [
PROFILE ,DELIVERIESHIDE ,SALESHIDE ,LOGISTICHIDE ,STORESHIDE
SUPER ,0 ,0 ,0 ,0
DELIVERIES ,1 ,0 ,0 ,0
SALES ,0 ,1 ,0 ,0
LOGISTICS ,0 ,0 ,1 ,0
STORES ,0 ,0 ,0 ,1
] ;

I've reloaded the file only with section access and works fine but when add section application things are going to hell. I'm out of ideas. Please help!

[marcus_sommer]

For a in regard to the UI performance not very optimized application might a qvw of about 800 MB already quite large. Are the 800 MB compressed or uncompressed? Means the real size might be to some degree larger.

Nevertheless the filesize isn't the issue but using a linkt-table datamodel and applying complex calculations within the objects is definitely not suitable to get a fast UI and this is independent to the use of section access or not.

I never used section access to control the visibility of the sheets because without also implementing section access as a data-reduction method the sheet-visibility is only a kind of usability but not an appropriate way to control confidential data. I don't have much need to show/hide sheets and if I use something like:

match(osuser(), user1, user2, ...)

I don't know why the section access slow down your application so massively but I assume that your sheet-control data have the effect to create a kind of cartesian product for the virtual tables on which the objects are created. Those virtual tables are the dimensional context on which the calculations will be performed - and this creation is the biggest bottleneck in the overall calculation because it's always a single-threaded execution.

Therefore my suggestion:

• check if the control of the sheet-visibility is enough or if also the data need an access control
• considering to switch from a section access control of the sheet-visibility to a check of the osuser()
• optimizing the datamodel by changing from a link-table to a star-scheme
• avoiding complex calculations within the UI - especially avoiding aggr(), nested if-loops and interrecord-functions

- Marcus

[papanovn · Accepted solution]

Problem is solved. The reason was a list box with option "Show as TreeView". The field has over 800K values, so obviously show as treeview doesn't work well with such number of values. This list box was on every page and was hiding when I comment the section application. That mislead me that section application is the reason for the slow work.