How to optimize OutSystems paging query performance? When we use low-code platforms such as OutSystems to develop projects, we cannot do without the backend management functions of the web. Among the common backend management functions, the data list paging function is a core function, because we need to complete the management of various businesses in the background, such as orders, transactions, commodities, etc.
These data are all implemented using OutSystems’ Table, and require paging, sorting and other functions. I found in actual projects that the paging function of OutSystems has performance issues, especially after the system has been online for a period of time and the amount of data is large, the backend will obviously feel slow.
Using a low-code platform to develop a project is a double-edged sword. For team members, there may be many problems, such as slow development and deployment, conflict issues, version management, etc.; if team members can cooperate well and the project follows the best architectural practices, using a low-code platform will bring benefits, such as quick online results, WYSIWYG during development, parallel development of web and mobile, and developers do not need to worry about the database.
The corresponding guide for the best architectural practices when using OutSystems to develop projects can be found on the official website.
Architecture – OutSystems Best Practices
Okay, let’s return to the topic of OutSystems’ slow paging query performance issue in this article.
How to optimize OutSystems paging query performance
To understand the performance issues of OutSystems paging, you need to first understand how OutSystems implements the paging function. For example, the following figure shows a common paging function. This function can be completed quickly using OutSystems. A quick start tutorial is provided on the official website. If you are interested, you can click the link below to view it.
Pagination – OutSystems 11 Documentation
Next is the key point. For a seemingly simple paging function, what kind of SQL statement is generated by OutSystems to retrieve data? When we click on page 17, what kind of SQL statement will be executed? At this time, we need to use the SQL Server Profiler tool.
Connect to the SQL Server database on the Amazon cloud through the SQL Server Profiler tool.
How to optimize OutSystems paging query performance?
What SQL is executed by OutSystems paging query?
To find out what SQL statement is generated by OutSystems paging query, use SQL Server Profiler and add some filtering conditions. Otherwise, all the SQL statements being executed will be captured, which is not conducive to analysis. How to do it specifically?
Just set the filtering conditions when creating a new tracking attribute (you can scroll the window to the middle part and cancel the Audit Login and Audit Logout Out events, otherwise many tracking events will be generated), click on the column filter, edit the filter, I only set DatabaseID and TextData (when there are multiple databases, it is more accurate to capture SQL when filling in DatabaseID or DataBaseName. If you don’t know DatabaseID, you only need to execute SQL query: select DB_ID(‘Your database name’) to get it), as shown below:
How to optimize OutSystems paging query performance?
How to optimize OutSystems paging query performance?
How to optimize OutSystems paging query performance?
After setting it up like this, go to the website backend, refresh the list page, or click the page number to turn to the next page to see the executed SQL statement. In this example, I clicked on page 11 and saw that a statement similar to select top 110 was generated, as shown in the following figure:
How to optimize OutSystems paging query performance?
This basically confirms that the paging function of OutSystems queries all the data and then fetches the content of page 11 in the program, rather than directly fetching the content of page 11 through SQL as we expect. This also explains why the database server becomes slower and slower as the amount of data increases, and why the website management backend page also becomes slower.
I simplified the SQL executed by OutSystems paging query as shown in the following code:
EXEC sp_executesql N'
SELECT TOP (110)
[CMP].[EMAIL],
[INV].[TYPE],
[MEM].[NAME],
[USR].[ID],
[USR].[EMAIL],
[USR].[CREATED],
[MODE].[NAME],
[INFO].[ID],
[INFO].[NAME],
[INFO].[ID_NO],
[INFO].[PHONE],
[INFO].[ADDR],
[INFO].[COUNTRY],
[INFO].[DOB],
[STATUS].[NAME],
(CASE WHEN LEN(LTRIM(RTRIM(@selInvs))) = 0
THEN 0
ELSE (CASE WHEN CHARINDEX(N'','' + CONVERT(VARCHAR(11), [USR].[ID]) + N'','', @selInvs) <> -1
THEN 1
ELSE 0 END)
END) AS [SEL]
FROM
[DB].[dbo].[USR] [USR]
INNER JOIN [DB].[dbo].[INFO] [INFO] ON [USR].[ID] = [INFO].[ID]
INNER JOIN [DB].[dbo].[STATUS] [STATUS] ON [INFO].[STATUS_ID] = [STATUS].[ID]
LEFT JOIN [DB].[dbo].[INV] [INV] ON [INFO].[INV_TYPE] = [INV].[ID]
LEFT JOIN [DB].[dbo].[MEM] [MEM] ON [INFO].[MEM_ID] = [MEM].[ID]
LEFT JOIN [DB].[dbo].[CMP] [CMP] ON [INFO].[CMP_ID] = [CMP].[ID]
INNER JOIN [DB].[dbo].[MODE] [MODE] ON [INFO].[MODE_ID] = [MODE].[ID]
WHERE
[INFO].[TYPE] = 2
AND (LEN(LTRIM(RTRIM(@invType))) = 0 OR CHARINDEX(N'','' + CONVERT(VARCHAR(11), [INV].[ID]) + N'','', N'','' + @invType + N'','') <> -1)
AND (LEN(LTRIM(RTRIM(@memId))) = 0 OR CHARINDEX(N'','' + CONVERT(VARCHAR(11), [MEM].[ID]) + N'','', N'','' + @memId + N'','') <> -1)
AND (LEN(LTRIM(RTRIM(@modeId))) = 0 OR CHARINDEX(N'','' + CONVERT(VARCHAR(11), [MODE].[ID]) + N'','', N'','' + @modeId + N'','') <> -1)
AND (LEN(LTRIM(RTRIM(@statusId))) = 0 OR CHARINDEX(N'','' + CONVERT(VARCHAR(11), [STATUS].[ID]) + N'','', N'','' + @statusId + N'','') <> -1)
ORDER BY
[INFO].[CREATED] DESC',
N'@selInvs nvarchar(10), @invType nvarchar(10), @memId nvarchar(10), @modeId nvarchar(10), @statusId nvarchar(10)',
@selInvs = N'',
@invType = N'',
@memId = N'',
@modeId = N'',
@statusId = N''
How to optimize OutSystems paging query performance?
It can be seen that the SQL statement generated by OutSystems is directly associated with the Aggregate data source of the query, and then a top query is performed based on the page number. OutSystems paging query is slow, this is the reason.
How to optimize OutSystems paging queries?
Knowing the root cause of the problem, you only need to solve it accordingly. Simply pass in the page number, number of entries per page, query conditions, sorting rules, etc. as parameters. For higher versions of SQL Server, the core logic of paging query is as follows:
select * from znlive_table where 1=1 -- other filter order by 1 -- order by filter offset @pageNumber * @PageSize ROWS fetch next @PageSize ROWS ONLY
How to optimize OutSystems paging query performance?
This is a very simple SQL Server database paging query. In actual projects, it is also optimized directly through SQL, as shown in the following figure:
How to optimize OutSystems paging query performance?
This is the paging Action encapsulated in the Module. You need to define a Structure that encapsulates the returned data to carry the data.
How to optimize OutSystems paging query performance?
Some thoughts on OutSystems paging
In fact, when optimizing the paging problem, while the low-code platform is convenient, some performance problems are really troublesome if you don’t know how to solve them. When optimizing these functions, I want to encapsulate the paging action into a common one, similar to the paging method we wrote in C#, returning a generic List object, or an object similar to DataSet, DataTable. As a result, OutSystems does not support it. If there are many pages in the backend paging list and each one needs to be optimized, it is indeed a very large workload.
In short, there are the following disadvantages of not using the official paging method and handling paging by yourself:
- When writing the paging action, you need to establish a different structure for each list page.
- There will be a lot of SQL statements, and the distributed SQL statements cannot be shared.
- There are many repeated codes, and changing the table fields requires manual processing.
Although there are the above disadvantages, after this optimization, the backend product list can hardly be opened when turning pages when there are many users before optimization. Now it opens in seconds, and the effect is great. In view of this, if readers have better methods, please leave comments below.