Hey Andreia,

OQL is more user-friendly and efficient for complex queries than using multiple retrieves and processing results in memory. It handles joins more effectively by combining multiple entities in a single query.

OQL excels with large data sets, leveraging database-level optimizations, reducing the amount of data transferred to the Mendix runtime. It uses only the necessary fields to retrieve data, which reduces memory usage and increases performance.

Normal retrieves using XPath are simpler and more intuitive for straightforward data retrieval, where you just need to fetch objects based on basic conditions. They're easy to set up and maintain and are usually sufficient for basic CRUD operations and common data access patterns.

Check this doc:

- https://docs.mendix.com/refguide/oql/

- https://www.mendix.com/evaluation-guide/app-capabilities/data-querying-management/

I hope this helped,

Ricardo Pereira

Hey Andreia,

           Compared to using multiple retrieve activities, OQL is more efficient for data retrieval. By writing a single query, it reduces database costs. Additionally, when retrieving more than 100,000 records, OQL combined with a boolean variable can retrieve the data in seconds, making it much faster than using retrieve activities. However, the drawback of OQL is that when using nested clauses, it can take more time to retrieve the data.

             Therefore, if your scenario involves nested clauses, it is better to use retrieve activities. But if your scenario requires retrieving data with a single query clause, OQL is the better option.

              You were using OQL directly you check the Query by using OQL Snippet.

XPath will be translated to OQL, so it won't matter much in query size. However, with OQL it is possible to partially retrieve objects, where XPath will be translated to a query that always fetches complete objects.

Hello Andreia,

This might be an interesting read:

https://www.mendix.com/evaluation-guide/app-capabilities/data-querying-management/

OQL is mostly used for reporting where you need aggregation functions while grouping over certain attributes. Another reason to use OQL is to define custom security expressions, which you need to define manually. In some cases, this may result in faster queries if you handle security yourself using OQL than by using the out-of-the-box security of XPath.