Regarding Outsystems Aggregate pagination compare with using SQL offset and top

Referring to this post, https://www.outsystems.com/forums/discussion/42645/detailed-info-about-queries/,

Eduardo mentioned "this will not do any good on page 1000, as it will bring all records up to this page".

So if i were to set a Max Record of 50 and my users were to click page 1000, will Outsystems fetch all records up to page 1000 and then only present 50 rows to the screen and the remaining rows stays in the laptop memory?

How does it work actually?

Solution

Hi Newbie,

First, there's no way to tell a SQL database to only return rows 50-99 (for example). You can only tell it to return the top X rows. So if you need rows 50-99, you tell it to return the top 100 rows (0..99), and discard the first 50 so you can display rows 50-99. Now, if you are on page 1000, you need rows 49950..49999, so you need to tell the database to fetch 50000 rows, then discard 49950 of them, and only use the last 50. This is why you'll probably notice a slow-down when leafing through pages of data.

I say probably, because it depends on what the database decides to do when you request 50, especially when you have sorting, since in order to properly sort, the database may have to fetch all records anyway (depending on the indexes applied etc.), and fetching a larger set can fetch directly from the database cache.

Now there's the question about memory usage. My direct answer is "forget about that". The way the database server uses memory, the way IIS (on the web server) uses memory and the way the Platform uses memory is a big black box we needn't know about. However, it is very likely that though initially indeed all 50000 records are transfered from the database to the web server, all the 49950 unused records are quickly purged from memory by the garbage collector (a memory clean-up process part of the .NET platform OutSystems runs on). Since all OutSystems web apps run on the server, not on your laptop, there's definitely nothing of those 49950 unused records on the client computer.

Hope this helps a bit.

Solution

Kilian Hekhuis wrote:

Hi Newbie,

First, there's no way to tell a SQL database to only return rows 50-99 (for example). You can only tell it to return the top X rows. So if you need rows 50-99, you tell it to return the top 100 rows (0..99), and discard the first 50 so you can display rows 50-99. Now, if you are on page 1000, you need rows 49950..49999, so you need to tell the database to fetch 50000 rows, then discard 49950 of them, and only use the last 50. This is why you'll probably notice a slow-down when leafing through pages of data.

I say probably, because it depends on what the database decides to do when you request 50, especially when you have sorting, since in order to properly sort, the database may have to fetch all records anyway (depending on the indexes applied etc.), and fetching a larger set can fetch directly from the database cache.

Now there's the question about memory usage. My direct answer is "forget about that". The way the database server uses memory, the way IIS (on the web server) uses memory and the way the Platform uses memory is a big black box we needn't know about. However, it is very likely that though initially indeed all 50000 records are transfered from the database to the web server, all the 49950 unused records are quickly purged from memory by the garbage collector (a memory clean-up process part of the .NET platform OutSystems runs on). Since all OutSystems web apps run on the server, not on your laptop, there's definitely nothing of those 49950 unused records on the client computer.

Hope this helps a bit.

I see. Yes your explanation helps!

Glad I could be of help :). Happy coding!