[Demo optimized advance sql with Paging] Detailed info about queries

[Demo optimized advance sql with Paging] Detailed info about queries

  
Forge Component
(5)
Published on 22 Nov (4 weeks ago) by Shashi kant Shukla
5 votes
Published on 22 Nov (4 weeks ago) by Shashi kant Shukla

Hey, is it possible to provide some info about the queries structures to make the pagination more performant? I'm using OS 9 and can't see the eSpace but I am curious about it and surely the statements used in the Advanced Queries are available in OS 9

Thanks in advance,

VC

Hi Vincent,

To do like the OutSystems, you basically has to impose the Max Records property of the SQL query, using something like this:

List_Navigation_GetStartIndex(UserTable.Id,True)+UserTable.LineCount+1

This is what this component does.

The above technique will always bring all records from the first page to the actual page. And thus, the bigger the page, the performance decreases.

If you are ok with using specific DBMS features, you can use techniques to bring only the records for the page. But every DBMS will have a different way of doing it.

Cheers.

Hi Eduardo,

in fact, in our main application, we have a lot of screens that use advanced queries, because the queries are somehow complex to do them in an aggregate. 

In the max records field I indeed use the expression you referred: 

List_Navigation_GetStartIndex(UserTable.Id,True)+UserTable.LineCount+1

The question is, from what I read and from what I see ( If I'm correct ) this will limit the records displayed in the widget or even limit the records 'loaded' to the structure that I'm assigning the query output to but not the number of rows selected from the DB.

What I really want is to limit the number of records that I bring from the database simulating, for example, a TOP depending on how many rows we have per page.

Probably this also will have some problems ( when I go to the last pages of my list ) but when we are dealing with >1000 pages it is preferable to only bring the rows that will be displayed because sometimes we have timeouts. 

And even using the expression

List_Navigation_GetStartIndex(UserTable.Id,True)+UserTable.LineCount+1

I feel that every time I change page its slow as I assume the query runs again and brings all the results ( even limiting the number of records fetched )

We use SQL Server 2010 and I looked at this component and thought that simply the person who made it was using advanced queries with expressions limiting the results using LIMIT/OFFSET for example...

It would be great to know a 'standard' way to only SELECT the number of rows I need...


Cheers and thanks for reading,

VC

Hello Vincent,

When you set the Max Records properties of the SQL with the said expression, the platform will take this value and impose a TOP to the result.

But as you noticed, this will not do any good on page 1000, as it will bring all records up to this page.

Unfortunately, there is no SQL standard to select only a subset of the result based on the row position. 

This is easy in Oracle and MySql, that have SQL statements for that, but SQL Server introduced something (to be used with Order By) that does this only in the SQL Server 2012.

You can resort to the technique of using a subquery that return all IDs before the actual page, and apply the Top and a NOT IN to filter those records out, but this may be not possible or performance not wise depending on the query.

Cheers.

Eduardo Jauch wrote:

Hello Vincent,

When you set the Max Records properties of the SQL with the said expression, the platform will take this value and impose a TOP to the result.

But as you noticed, this will not do any good on page 1000, as it will bring all records up to this page.

Unfortunately, there is no SQL standard to select only a subset of the result based on the row position. 

This is easy in Oracle and MySql, that have SQL statements for that, but SQL Server introduced something (to be used with Order By) that does this only in the SQL Server 2012.

You can resort to the technique of using a subquery that return all IDs before the actual page, and apply the Top and a NOT IN to filter those records out, but this may be not possible or performance not wise depending on the query.

Cheers.

Okay 

But is that a true statment? In an "Advanced query" If I set a max records of 10 it Will do this:


SELECT TOP n *
FROM
(
    My Query
)

Is that correct? So it still selects all records from table right? 

Thanks for clarifying me 

Cheers

Hello Vincent, 

The Top N instruction, where N is the number of records you want to bring, will instruct the database (SQL Server) to fetch only N records at maximum.
When you set Max Records properties in the SQL statement, the platform will include the TOP N in the query.

So, assume the bellow command fetches 100 records as result:

SELECT * FROM Table;

The bellow command will fetches only 10 records:

SELECT TOP 10 * FROM Table;

Cheers.

Hey Eduardo,

Yes, I know that maybe I have not explained well. Supposedly on an aggregate if I put max records 10, OS will query the DB with top 10 and will in fact only bring those 10 records,

but, from what I heard about Advanced Queries Max Records is, if I set a max records, the query will still run in SQL server with NO TOP but then, the result of that query will be brought with the value I added in the max records.

So if I'm correct, the query will still bring all the results, even it will only add the max records I set in the parameter,

At least this is what have been told to me,

Cheers and thanks again Eduardo, for discussing this with me! As it can be really important for performance issues...

Vincent Colpa wrote:

Hey Eduardo,

Yes, I know that maybe I have not explained well. Supposedly on an aggregate if I put max records 10, OS will query the DB with top 10 and will in fact only bring those 10 records,

but, from what I heard about Advanced Queries Max Records is, if I set a max records, the query will still run in SQL server with NO TOP but then, the result of that query will be brought with the value I added in the max records.

So if I'm correct, the query will still bring all the results, even it will only add the max records I set in the parameter,

At least this is what have been told to me,

Cheers and thanks again Eduardo, for discussing this with me! As it can be really important for performance issues...

Hello Vincent,

This may be true. I didn't hear that, but as the query can be very complex, it may be difficult to detect where to put the TOP clause.

In any case, if you want to have the same MaxRecords functionality of the Aggregate, you can always set a TOP clause in your query and so guarantee that the database does not fetch all the records.

I'll take a look to see the behavior of the MaxRecords in an SQL.

Cheers.

Hey,

thank you so much for answering and taking it in account, imho right now I don't have the availability to look into it but probably checking the query being executed in 'Physical Side' will give you an hint of what is the query really doing, but I heard of a colleague of mine that indeed works like that, that is why I was so curious about this component, because I was thinking that the creator indeed was using some 'ninja' parameters to in fact limit the number of results,

this is always something that we usually ignore when we use advanced queries, but we really need to be careful because comparing to Aggregates they are in fact not optimized, not even a bit

Cheers 

A simple test shows that at least regarding testing a SQL, the MaxRecords value is ignored (and this makes me think that in run time it will be the same...).

So, my suggestion in this case is always set the TOP or LIMIT or whatever is the syntax on the target database to limit the number of records directly in the query, to avoid the extra work of the database... 

Eduardo Jauch wrote:

A simple test shows that at least regarding testing a SQL, the MaxRecords value is ignored (and this makes me think that in run time it will be the same...).

So, my suggestion in this case is always set the TOP or LIMIT or whatever is the syntax on the target database to limit the number of records directly in the query, to avoid the extra work of the database... 


Awesome! And that is important and should be refered in Max Records description in the case of advanced queries since it really looks like it will limit the records fetch but in fact its not true

Thank you so much, everyday we learn something

Hello Vincent,

I was searching and I found this post that is very interesting: https://www.outsystems.com/forums/discussion/12150/max-records-possible-bug-on-advanced-queries/

You may want to check it.

Hello Eduardo,

in fact, it is an interesting post, because when we start looking into performance issues, this will definitely be something to look in,

soon I will have less work and will start looking into queries for optimization and will check the relation between performance/volume of data when using statements for bringing limited results,

I will soon post about it,

Cheers

VC