[AdvanceQuery] How can I pagination on SQL server using Advanced Query component?

[AdvanceQuery] How can I pagination on SQL server using Advanced Query component?

  
Forge Component
(6)
Published on 2017-05-13 by PAULO RICARDO FAGUNDES JUNIOR
6 votes
Published on 2017-05-13 by PAULO RICARDO FAGUNDES JUNIOR

Hello guys, in this post I going to teach you how to make pagination using my component Advanced Query.

Advanced Query is a component that allows you to do queries on database the way more dynamic, you can get more control about your queries and you do not to need mapping your database, therefore, you will not need to do refreshes every espaces on each change that you to do like Integration Outsystems.

All developers knows how many the pagination is important to improve performance of our application.


OK, let is to example: 

In first step we will need two structs, Counter and Countries:

Counter (to get counter of records) 

  • Count - Integer

Countries (to get data of our table)

  • Number - Integer
  • Name - Text

 

On the next step we can create our page and local variables:

LineCount is default value 5.


In preparation of the new page we will add the follow actions:


To get the data pagination we will need to execute the command:

This command is working only on the SQL server. In another post I will give more tips how you to do on Oracle and MySql.


"DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = "+List_Navigation_GetStartIndex(TableRecords1.Id,True)+"
SET @RowspPage = "+LineCount+"
SELECT * FROM (
             SELECT ROW_NUMBER() OVER(ORDER BY Id) AS NUMBER,
                    Nome FROM Paises
               ) AS TBL
WHERE NUMBER BETWEEN (@PageNumber + 1) AND (@PageNumber + @RowspPage)"


Now let's see the widgets:

The TableRecords1 is getting like Source Record List the ContriesList (local variable).

The property Line Count is getting the LineCount (local variable), and then Start Index property is getting value 0.


The widget List_Counter:

StartIndex is getting the code below:


If(Pag = 0 ,List_Navigation_GetStartIndex(TableRecords1.Id,True),List_Navigation_GetStartIndex(TableRecords1.Id,True,Pag))



The widget List_Navigation:



The OnNotify RefreshTable is using the same action we used in the preparation, but before we will need to clear the variable CountriesList.



I hope this is helpful for someone.
Thanks.




Hi Paulo, thanks for sharing this how to. It is written clearly. I was wondering if you did performance tests on the solution presented. Could you share your findings? How big was the table? How many and how big were the columns? Does the fetch time increase as we go to later pages?

Greetings, Pedro


Hi Pedro, you have a good point. Actually, I didn't these tests, because I cannot get a real case, I developed in my personal area and connected me in a SQL server at home. So, the internet not so good to get a real case.
But, the important thing is the sql command, you can use in my extension and also you can use in outsystems advanced query. Anyway, I can try to get some results for us.

Thanks for question.

Regards, Paulo Ricardo.


Hi Paulo, it would be interesting. For instance, a table with 2 million records on it. It doesn't matter what, for instance countries with country name and country code. As I stated previously, I would expect the time to fetch the data to increase as the user goes to later pages. This is because the database has to traverse all records from the first one onwards to reach the ones that will be returned. As more records have to be traversed as we go to later pages, more I/O will take place and, therefore, more time will pass by. To check this, the actual time to retrieve the first page does not matter. What matters is how the time increases as we go to later pages. Does it increase linearly? I would expect so.

I'm curious if this is actually the case or if the time actually remains constant.

Greetings, Pedro

Hi Paulo,

Can you please share this Pagination oml file.


Thanks,

Ronan.

Solution

Ronan T wrote:

Hi Paulo,

Can you please share this Pagination oml file.


Thanks,

Ronan.


Hi Ronan, unfortunately I no longer have that eSpace.

If I can help you in any other way, let me know.

Solution

Hi, Thanks for sharing.


Have you tried OFFSET and FETCH for pagination? I used this approach for our Kendo Grid Server Side. 

OFFSET @skip ROWS
FETCH NEXT @take ROWS ONLY
  

Jovvy wrote:

Hi, Thanks for sharing.


Have you tried OFFSET and FETCH for pagination? I used this approach for our Kendo Grid Server Side. 

OFFSET @skip ROWS
FETCH NEXT @take ROWS ONLY
  

Hi, actually I don't remember. I've tried keep the same way that Outsystems make.

I found the Space, you can see on the HomePage's preparation.