Performance issues Universal GUI

@Dick van den Brink any comment from Thinkwise here? Why are look-ups no direct lookups? 

This is on our backlog to research.

The plan is to only gather lookup translations for columns which are visible.

Also the performance problem you mentioned on a slower base subject will come by in the research.

As a workaround. you may consider integrating the lookup translation in the subjects view itself on time consuming subjects.

Why all lookups are based on the base-table with a left join? Why not directly lookup on the lookup table?

The reason for this is that the Universal GUI might not have access to all reference fields that are required to select the translations directly from the lookup table.

We are aware of the potential impact on performance, especially for slower base tables, and are investigating possible alternatives and optimizations.

@Jasper @Dick van den Brink 

The reason for this is that the Universal GUI might not have access to all reference fields that are required to select the translations directly from the lookup table.

I don't quite follow this. This is setup related? Or authorization related? I'm really a bit struggling with this, because integrating lookup values in the view take a way the really needed look-up popups to show detailed information (as ref popups do not exist yet). In our case this view is the most used view and is connected to everything..  So with pagination and 8-10 lookups ti's quite a challenge to keep it performing because of the fact that the base view gets included in everything.  

I really hope that the research will lead to a little break-through perhaps that at least for the lookups it can do them directly (validate the needed access upfront) and for pagination perhaps a good addition can be to have an alternative count query in case of bloated views (that have mandatory pre-filters for speed).  

Hi Freddy, 

Do you see any possibilities to improve the performance of the base view, for example by optimizing the SQL, persisting the data or adding indexes? This would greatly benefit the performance of the lookups as well.

Regarding your question about the reason being setup or authorization related: this restriction is caused by improved column authorization, which was introduced in the 2021.1 release.

Like I said, we are looking at possible optimizations in both the Universal GUI, for example by expanding the lookups with the initial select.

Freddy's comments are completely consistent with our findings and the performance issues we are having with the Universal GUI.
A view in combination with look-ups gives a dramatic performance when using the Universal GUI. Certainly in the way in which the TW platform handles refreshing data.

We are currently in the process of removing all kinds of look-ups from our views, and then including the information directly in the views. Definitely not a desirable situation.

We are on the "eve" of a major conversion project, but we are unsure whether we should use the Universal GUI because we are experiencing serious performance problems in the existing application.

Performance with full use of the Universal GUI (including look ups, etc.) is currently not sufficient, especially when using mobile devices.

In our opinion, this point must be improved in the short(er) term, so that we can fully use the Universal GUI.

Hi @Jasper, 

Yes, I've done all the optimalizations.. my solution for now is to calculated timings by a system flow that runs every minute, so timings are just a read-action from a regular table which of course resolves (in our case) almost all issues..  that said.. I would like it real time and for a single project I accept the loading time.. but as said 10x this loading time is not acceptable.  

We'll make it work, however it's not how it should work and that's a bit the point. 

I am looking forward to the research findings.. I believe tackling this problem will benefit hugely the overal U-GUI performance. 

Hi all,

We recognize this problem and we are looking into various alternatives.

Dueling with the cost-based optimizer of SQL Server can be a bit of a challenge. I’d like to verify a couple of things - specifically for your scenario.

First - can you let us know the performance of the query when using an inner join?

declare @handle int;
exec sp_prepexec @handle output, N'@p0 int, @p1 int, @p2 int, @p3 int, @p4 int, @p5 int, @p6 int, @p7 int, @p8 int', N'SELECT t1.[process_id], t1.[process_task_id], t1.[tenant_id], t2.[description] AS [lookup_636502171]
FROM [process_task_view] t1
JOIN [tenant_definition_status] t2
 ON t1.[tenant_id] = t2.[tenant_id]
 AND t1.[process_status_id] = t2.[domain_status_id]
WHERE (t1.tenant_id = convert(int,SESSION_CONTEXT(N'tenant_id'))
)
 AND ((t1.[process_id] = @p0 AND t1.[process_task_id] = @p1 AND t1.[tenant_id] = @p2) OR (t1.[process_id] = @p3 AND t1.[process_task_id] = @p4 AND t1.[tenant_id] = @p5) OR (t1.[process_id] = @p6 AND t1.[process_task_id] = @p7 AND t1.[tenant_id] = @p8))', @p0=3087, @p1=143, @p2=1, @p3=3081, @p4=116, @p5=1, @p6=3083, @p7=126, @p8=1

Second - can you let us know the performance of the query that does not join the look-up but only selects the foreign key columns?

declare @handle int;
exec sp_prepexec @handle output, N'@p0 int, @p1 int, @p2 int, @p3 int, @p4 int, @p5 int, @p6 int, @p7 int, @p8 int', N'SELECT t1.[process_id], t1.[process_task_id], t1.[tenant_id], t1.[process_status_id]
FROM [process_task_view] t1
WHERE (t1.tenant_id = convert(int,SESSION_CONTEXT(N'tenant_id'))
)
 AND ((t1.[process_id] = @p0 AND t1.[process_task_id] = @p1 AND t1.[tenant_id] = @p2) OR (t1.[process_id] = @p3 AND t1.[process_task_id] = @p4 AND t1.[tenant_id] = @p5) OR (t1.[process_id] = @p6 AND t1.[process_task_id] = @p7 AND t1.[tenant_id] = @p8))', @p0=3087, @p1=143, @p2=1, @p3=3081, @p4=116, @p5=1, @p6=3083, @p7=126, @p8=1

Hi @Anne Buit, 

the timings:

1. Original
2. with INNER join lookup
3. without lookup

(2 rows affected)

 SQL Server Execution Times:
   CPU time = 2048 ms,  elapsed time = 4241 ms.

 SQL Server Execution Times:
   CPU time = 2204 ms,  elapsed time = 4700 ms.
SQL Server parse and compile time: 
   CPU time = 172 ms, elapsed time = 363 ms.

(2 rows affected)

 SQL Server Execution Times:
   CPU time = 1737 ms,  elapsed time = 4289 ms.

 SQL Server Execution Times:
   CPU time = 1953 ms,  elapsed time = 4688 ms.
SQL Server parse and compile time: 
   CPU time = 203 ms, elapsed time = 344 ms.

(2 rows affected)

 SQL Server Execution Times:
   CPU time = 1908 ms,  elapsed time = 2766 ms.

 SQL Server Execution Times:
   CPU time = 2156 ms,  elapsed time = 3137 ms.

Hi @Anne Buit 

Thanks for the elaboration on this point. For me the strategy you have in mind the gains would definitely outweigh the named drawback. 

• As for point 1, I don't think the U-GUI should compensate for the lack of rights. This could be a great validation, because when you have give rights to a field with a lookup, this immediately means that you'll need the rights on the reference meaning source table & target table including key fields and the lookup field on the target table. This all lies in the model and can be predicted up-front I believe. 
• As for point 2, this is always an interesting topic, because you are entering the subject of the lookup state, which actually should serve 2 states (the same as the ‘available’ checkbox with elements in domains)..   there is an ‘active’ lookup where you use it for getting the values to fill a field.  And there is the ‘passive’ lookup that used for listing purposes.. I can have records or elements not available for active lookups.. but I almost see no reason why you would limit records/elements for listing purposes. Perhaps there can be 2 lookup states or some kind of default override for listings. 

As a general point of opinion, I rather have a default lookup return with a default value of ‘unauthorized’ than the current separate lookup queries. 

If we want the look-up filter columns for look-ups to be granted to the users, we can automatically do this via the roles. Said columns would remain hidden instead of unauthorized. This is also done for columns like primary key columns as the UI always needs them.

Your last suggestion is definitely worth considering - even though it would be a breaking change for products.

Note that a joined look-up with the base table is always required when filtering or sorting is done based on the look-up translation. This could lead to some odd scenarios where you could derive the value of an ‘Unauthorized’ look-up by looking at the impact of filters and sorting on the look-up column.

Thanks for all the input!