I have a difficult issue.
My SQL return 1 milion records. I need sort it and show 100 records in my screen. From 300th to 400th.
What is the best way to improve performance?
Please help me.
Hey Minh,
You should look into pagination and offsetting records.
Queries are as follow:
SELECT *
FROM Table
ORDER BY ID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
Take a look into the following forum posts as well:
https://www.outsystems.com/forums/discussion/56770/sql-offset-and-os-pagination/
https://www.outsystems.com/forums/discussion/56822/reactive-web-application-paginate-a-table-with-an-advancedsql-source/#
Regards,
Gonçalo
Hi Minh Quang Nguyen,
As Gonçalos said, look at the SQL functionality of offset and fetch next. For performance basis, you need to get the current set of page data at a time while sorting the records.
Please have a look at the below link for more info.
Offset Fetch Clause
MS SQL Server Doc
what I would do in the first place is to select only the field that you want the order by (let's call it ID),
order by it, select the rows with the offset/fetch clause and the with that result select the rest of the fields of the table, selecting in one go the fields, order them what a great amount of memory/swap could occur.
At the end will be two selects, the first with only the field that is going being ordered/selected and next select the rest of the fields with an in expression or union expression.