Skip to main content
Solved

how do I Increase table grid performance?


Forum|alt.badge.img+3

I’ve noticed while doing a optimization of a table for universal & WEB Gui, that going from the first page to the last page takes much longer in universal than in WEB Gui.

In WEB GUI this takes about 1.5 seconds and in Universal this takes about 12 seconds.

Does anyone have any idea what could be causing this?

 

Best answer by Freddy

Blommetje wrote:

Hi,

Chipping in here for my coworker Jaycee.
 
We have a table with a quite some columns, and roughly 6k rows. We have removed all Expressions - and this has increased performance tremendously. Toggling prefilters and searching is now quite acceptable (not good enough) - but when jumping to the last page, the query is quite expensive. 

When we look at the query from the debugger and remove the last line 

<rest of the query>
ORDER BY t1.[closing_time] DESC, t1.[shipment_id] ASC, t1.[vgm_closing_time] DESC
/*
OFFSET 5600 ROWS FETCH NEXT 25 ROWS ONLY
*/

WITH the offset - the query takes roughly 15 seconds, but when we remove the OFFSET, it is faster. 

The table performs fairly ok, but when jumping to the last page, it lags (the offset being in play). This only happens in universal gui. WEB gui is way faster. Other elements like the prefilters are comparable. 

Any thoughts on this? 

Blommetje

@Blommetje @Jaycee_C I think the difference between de WebGUI and Universal is that in the WebGUI pagination probably is not done server-side. The universal GUI does, hence the offset-fetch construction. Basically it wille refetch the query and will only return the result-rows 5600 to 5625 in your case. 

Better to use filtering, as it reduces the query-result as well. And make sure you have a specific and optimal index for this query, allowing SQL server to get the result as optimal as possible. 

 

 

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

5 replies

Mark Jongeling
Administrator
Forum|alt.badge.img+23

Hi Jaycee,

Performance is a broad topic. It can be a complex view, lots of expression fields, lots of big data maybe with multiple images, suboptimal indexes, among more.

Can you tell more about the screen you are experiencing problems with? 


Blommetje
Forum|alt.badge.img+13
  • Partner
  • 209 replies
  • January 23, 2024

Hi,

Chipping in here for my coworker Jaycee.
 
We have a table with a quite some columns, and roughly 6k rows. We have removed all Expressions - and this has increased performance tremendously. Toggling prefilters and searching is now quite acceptable (not good enough) - but when jumping to the last page, the query is quite expensive. 

When we look at the query from the debugger and remove the last line 

<rest of the query>
ORDER BY t1.[closing_time] DESC, t1.[shipment_id] ASC, t1.[vgm_closing_time] DESC
/*
OFFSET 5600 ROWS FETCH NEXT 25 ROWS ONLY
*/

WITH the offset - the query takes roughly 15 seconds, but when we remove the OFFSET, it is faster. 

The table performs fairly ok, but when jumping to the last page, it lags (the offset being in play). This only happens in universal gui. WEB gui is way faster. Other elements like the prefilters are comparable. 

Any thoughts on this? 

Blommetje


Blommetje
Forum|alt.badge.img+13
  • Partner
  • 209 replies
  • January 23, 2024

Or - when there any options for this - perhaps we can preload something in the background. Currently we do not this, but that might be nice. Have things loaded in the background… 

 


Forum|alt.badge.img+17
  • Moderator
  • 762 replies
  • January 23, 2024

The problem with offset is the higher the number becomes the slower the query. This means once you reach the last page the performance will take a dip. A few questions you could ask from a user perspective. do you need to see 6k of rows, or could you apply some filtering to limit this? Is the sorting order correct? Could you apply some manual filtering? Also did you check the execution plan if there are any missing indexes? 


Freddy
Forum|alt.badge.img+16
  • Thinkwise Local Partner Brasil
  • 529 replies
  • Answer
  • January 23, 2024
Blommetje wrote:

Hi,

Chipping in here for my coworker Jaycee.
 
We have a table with a quite some columns, and roughly 6k rows. We have removed all Expressions - and this has increased performance tremendously. Toggling prefilters and searching is now quite acceptable (not good enough) - but when jumping to the last page, the query is quite expensive. 

When we look at the query from the debugger and remove the last line 

<rest of the query>
ORDER BY t1.[closing_time] DESC, t1.[shipment_id] ASC, t1.[vgm_closing_time] DESC
/*
OFFSET 5600 ROWS FETCH NEXT 25 ROWS ONLY
*/

WITH the offset - the query takes roughly 15 seconds, but when we remove the OFFSET, it is faster. 

The table performs fairly ok, but when jumping to the last page, it lags (the offset being in play). This only happens in universal gui. WEB gui is way faster. Other elements like the prefilters are comparable. 

Any thoughts on this? 

Blommetje

@Blommetje @Jaycee_C I think the difference between de WebGUI and Universal is that in the WebGUI pagination probably is not done server-side. The universal GUI does, hence the offset-fetch construction. Basically it wille refetch the query and will only return the result-rows 5600 to 5625 in your case. 

Better to use filtering, as it reduces the query-result as well. And make sure you have a specific and optimal index for this query, allowing SQL server to get the result as optimal as possible. 

 

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings