Welcome to OutSystems documentation
»
Best Practices
»
Performance Best Practices
Last updated: June 24, 2017 2:39:26 AM GMT+07:00
In multi-tier applications, the bottleneck is often situated at the database level, namely in the queries. The importance of these practices is more notable when dealing with external/linked servers. Specific knowledge about the database engine will help in optimizing your queries. However, if you follow the next set of practices, you’ll considerably diminish your application’s performance drawbacks.
1. Don’t do joins over linked servers
Save as PDF
1.1. Description 1.2. Solution 1.3. Importance 1.4. Remarks 2. Minimize the number of fields fetched from the database 2.1. Description 2.2. Solution
Cross server joins are very inefficient. Avoid them at all costs.
2.3. Importance 2.4. Remarks 3. Keep Max Records consistent with your needs 3.1. Description
When using linked servers, avoid the cross server joins.
3.2. Solution 3.3. Importance 3.4. Remarks 4. Use SQL queries for bulk operations
The table in the linked server is completely loaded to the DB server and then the join is performed.
4.1. Description 4.2. Solution 4.3. Importance 4.4. Remarks
Inner joins with foreign entities tend to result in slow queries. However, if you must make a join over a linked server, make sure to use additional logic so that only a data subset is pulled from the linked server and only what was pulled will be joined (temporary tables, OPENQUERY, ...). Whatever the case, joins over linked servers need to be addressed very carefully.
5. Avoid using isolated Aggregates 5.1. Description 5.2. Solution 5.3. Importance 5.4. Remarks 6. Iterate only once and avoid using indexers ([ ]) over a query 6.1. Description 6.2. Solution
Avoid passing large Records/RecordLists as parameters of actions because this results in the platform fetching all the queried fields.
6.3. Importance 6.4. Remarks 7. Minimize the number of executed queries 7.1. Description
When dealing with actions that receive or output Record/RecordLists with a large number of attributes consider using structures instead of entities for those records. Structures will narrow down the amount of data that flows through the entire system producing significant net benefits.
7.2. Solution 7.3. Importance 7.4. Remarks 8. Avoid ReturnedRowCount (version 4.2 or below) 8.1. Description
In order to minimize the size of data fetched from database and to prevent slow queries and excessive memory usage, the compiler optimizer automatically detects which fields from query result records are used. The unused fields are unnecessary and as such are not fetched from the database. However when the records of a query result are used as argument of an action the optimizer doesn't have enough information to determine which fields are used and which fields aren't. In this situation all the fields will be fetched from the database which in some cases might be a prohibitive amount of data.
8.2. Solution 8.3. Importance 8.4. Remarks
There is a balance that needs to be taken into account between maintainability and performance. Using entity records as query results and input/output parameters usually results in highly reusable easy to change components. This best practice should only be used when dealing with entities/query results with a large number of fields.
Keep the Max Records property of the Aggregates and SQL queries, consistent with the amount of data that you're displaying.
When there are limitations to the amount of records that will be fetched from a query, you should fill the Max Records of the query accordingly to optimize the query execution time. This is especially useful in table records or when an Aggregate is used to get a single record.
Usually, we don't need to display thousands of records in a single screen, so there's no need to get all of them from the database. Only get the amount of rows that will be displayed. This improves screen loading.
In previous versions of the OutSystems Platform (before version 4.2) the SQL queries didn't have the Max Records property. You had to add the clause in the query, but the same considerations apply.
When performing massive operations use SQL queries instead of using a foreach loop with Aggregates (keep in mind that Aggregates are always more built to change, though). Also updates and massive deletes are faster in SQL queries than using Entity Actions. Write queries that update as many rows as possible in a single statement rather than using multiple queries to update the same rows.
1. If you do not need to retrieve all Entity Attributes, consider replacing the Get invocation by an Aggregate using an Identifier parameter. The Platform will optimize the query to retrieve the required attributes. 2. f you don't need to update all Entity Attributes, consider replacing the Update invocation by an SQL Query using an UPDATE SET statement, setting just the required attributes. The Platform also uses an UPDATE SET statement, but always updates every attribute. 3. If you need to delete multiple records, use an SQL Query with a single Delete instead of a For Each followed with a Delete operation for each record. 4. Use Update instead of a Delete followed by a Create . 5. Use a CreateOrUpdate instead of a Select followed by a Delete and a Create. 6. Use an SQL query with a sub query instead of an Aggregate, followed by a for-each loop over the query result with another simple query for each record.
Every operation done against the database pays a round trip cost of communicating with the server. When doing a large number of operations the sum of all the round trip cost is quite significant; so there is a real benefit in reducing the number of operations. Also the databases are significantly more efficient at performing batch operation than many small ones. There is also a significant benefit in not retrieving information that isn't necessary.
This is a very important best practice, and one that is not being used very often, mostly due to lack of experience of many developers, that feel more comfortable using Aggregates and avoid SQL queries at all cost.
Never create an action to execute an Aggregate. When the query is isolated in an action by itself, the Platform can't optimize the number of fields to be retrieved.
The compiler optimizer automatically detects the used fields. However, when calling an action whose output is an entity or record list, the entity (or entities in the case of a record list) will be fetched entirely from the DB.
This is even true even if the action doesn’t read all the fields of the entity/record list. This will cause unnecessary database overload and unnecessary memory usage.
Please note that this invalidates code reusability - so, in some cases, (especially when the output is not large) it is better to have isolated queries instead of a lot of similar ones.
Iterating more than once over a query result is not a good practice. By doing so, the query results will be copied into the memory. The same applies when using direct indexers (like query[2].value expressions).
If conserving server memory is necessary, try to avoid doing multiple iterations (for each, table/list records) over the same query. It is better to do another query in order to execute the second iteration (Note: if DB is the bottleneck, this doesn’t apply).
When doing multiple iterations on the same query, the query results are automatically loaded into memory. If the query returns a lot of results this may increase memory consumption substantially, especially if the page is loaded very often. Using indexers on your expression also loads the results in memory independently of the number of iterations.
This also applies to lists displayed on a screen. For example if you iterate a query once and then display it in a table records, your query will be copied into memory. This happens because the rendering of the table records iterates through the query results again.
Minimize the number of executed queries. Often it is possible to fetch all necessary data in a single query execution instead of multiple ones.
Whenever possible, group several Aggregates into a single one to avoid unnecessary trips to the database. Be especially weary of queries inside for-each loops as they usually lead to severe performance problems.
Even the simplest of queries has to pay the round trip cost for contacting the database. Furthermore, the net cost of loading and querying data is usually lower if it's done in a single query instead of multiple queries.
While minimizing the number of executed queries can yield large performance gains, it is usually done at the expense of readability. So the key here is to optimize only when required. Use all Service Center reports to pinpoint the bottlenecks. Remember that fast queries won't show up on the slow queries report but, if they are executed often, their aggregate time will influence all other logs (screen logs, web service logs etc.)
Avoid using a query only to look at the ReturnedRowCount value.
Use an SQL query with a count instead.
Before OutSystems Platform version 4.2, when using ReturnedRowCount the query output goes to memory. Also the Max. Records attribute was mandatory so the query could return an incorrect value.
This is only worth the trouble for queries that can return a large number of results. If your OutSystems Platform version is below 4.2, please contact OutSystems Technical Support for further assistance. Back to top
Performance Best Practices - Logic Performance Best Practices - References
Was this article helpful?
Performance Top 10 Rules Good applications have at least one thing in common – they are really fast. Beyond built-in OutSystems Platform optimizations, keep these 10 guideline... Performance Best Practices - Data model The data tier of the applications is absolutely critical for application performance. Apply these practices right from an early design phase. Performance Best Practices - Logic It is the developer's responsibility to maintain a high standard of coding that always takes performance into consideration. SQL Server Best Practices
Article type: Topic
Owner: Experts
Company
Stage: Draft Tags: support-application_development, support-Database, support-development, support-maintenance
Support
Corporate Site
Technical Support
OutSystems© All rights
Contacts
Community Forums
reserved. Custom built with and