Solved

Performance issues Universal GUI

  • 2 February 2023
  • 12 replies
  • 208 views
Freddy
Userlevel 5
Badge +13
  • Freddy
  • Thinkwise Local Partner Brasil
  • 269 replies

How do I turn this off? 

I have a view that calculates real-time timings, however I notice that because of lookups the view gets queried once for itself but then also for every lookup. There are many performance issues with this:

  • Why the OR statements and not the actual pre-filter, they tend to be less performant than the exists clause in the pre-filter.
  • Why all lookups are based on the base-table with a left join? Why not directly lookup on the lookup table?

This setup is really killing me. Any advice here on how to optimize this situation?

 

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
LEFT OUTER 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

 

icon

Best answer by Anne Buit 14 February 2023, 16:22

View original
LEF - The future favors the brave. [lef.tec.br]

12 replies

Freddy
Userlevel 5
Badge +13

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

LEF - The future favors the brave. [lef.tec.br]
Erik Brink
Rank
Userlevel 5
Badge +5

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.

Jasper
Rank
Userlevel 7
Badge +11

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.

Freddy
Userlevel 5
Badge +13

@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).  

 

LEF - The future favors the brave. [lef.tec.br]
Jasper
Rank
Userlevel 7
Badge +11

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.

M
Rank
Userlevel 2
Badge +6

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.

Freddy
Userlevel 5
Badge +13

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.

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. 

LEF - The future favors the brave. [lef.tec.br]
Anne Buit
Rank
Userlevel 6
Badge +5

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

 

Freddy
Userlevel 5
Badge +13

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.

 

LEF - The future favors the brave. [lef.tec.br]
Anne Buit
Rank
Userlevel 6
Badge +5

Thank you for the update. The view in question takes at least 3 seconds to gather the information required to translate the look-ups. This is unfortunate, there are no quick solutions with the current look-up translation strategy.

Currently, accessing the base table is mandatory for look-up translation. It is important that the look-ups are correlated with the base table for two reasons:

1. Column-level authorization on the base- and look-up table

The user may not have rights on the columns required to filter the data in the look-up table, either in the base table or the look-up table.

This is a security measure but it is something that could be changed: If you have access to look-up column x, we could automatically assign rights on all look-up filters for column x in both the source and target table. This would provide the UI with all information required to directly access the look-up table of look-up column x to gather translations, fill comboboxes and such.

2. Row-level authorization on the look-up table

The user may not have rights to the records containing the look-up translations. For instance, due to locked prefilters.

When using the look-up translation navigation path (/transl_x), Indicium will not apply row-level authorization on the look-up table. This allows you to obtain translations present in records that you would not be able to access directly via the api on the look-up table. Row-level authorization is applied on the main table to prevent access to arbitrary look-up translations.

There is no good way to work around this. If the UI wants to access the translations directly on the look-up table, there is no row-level authorization on the base table to keep things in check. We would have to choose between either presenting the user with untranslated look-ups when the translation is unavailable due to row-level authorization, or we would allow any arbitrary look-up translations to be obtainable, even when the translation is not in any way related to an accessible base table record.

 

So what now?

We are currently looking into a strategy where all look-ups are translated with the same request as the base data. This would allow look-up translations to piggy-back on the already executing base data selection while the main table data is still being processed in a safe environment. The correlation with the main table would remain intact.

This means all look-ups are translated in a single http request, together with the main table data.

This will greatly reduce the total duration for all look-up translations when the base table is slow, such as with your view. The drawback is that a single slow look-up may slow down the entire request, whereas separate requests would result in a more responsive UI.

Freddy
Userlevel 5
Badge +13

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. 

 

 

LEF - The future favors the brave. [lef.tec.br]
Anne Buit
Rank
Userlevel 6
Badge +5

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!

Reply


Terms & ConditionsCookie settings