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
View original
+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?
+12
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
+12
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?
+16
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
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.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.