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? 

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.tclosing_time] DESC, t1.tshipment_id] ASC, t1.tvgm_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

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… 

 

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? 

<rest of the query>
ORDER BY t1.tclosing_time] DESC, t1.tshipment_id] ASC, t1.tvgm_closing_time] DESC
/*
OFFSET 5600 ROWS FETCH NEXT 25 ROWS ONLY
*/

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