Let's walk through creating a standard Build VM
Part 6 - Fetching Data with an Order By
What does using OrderBy() with OData do to performance?
When querying OData endpoints for data, it may be required to arrange the result that you are getting from Finance and Operations. But what is the best way to do that and what are the performance implications? Let's take a look. To review the code for this, please see https://github.com/NathanClouseAX/AAXDataEntityPerfTest. This article uses the Part6 project for the data collection and the X++ project for the data entities.
We'll be running the tests below to determine what, if anything, using an Order By (OrderBy()) has on entity performance. We will be looking at Sales Orders only using data entity SalesOrderHeadersV2. All queries have been verified to be querying with conditions that will get at least 10 records for testing purposes.
Read 10 With Order By Descending Not on an Index - We're going to read 10 Sales Order Headers with an order by clause in the OData query with the order by descending specifically not being used on a field in an index
Read 10 with Order By Ascending Not on an Index - Same as the previous test but with an order by ascending
Read 10 with Order By Descending On a calculated field - We're going to read 10 Sales Order Headers with an order by clause in the OData query with an order by descending on a calculated field. Calculated fields are SQL Sub-selects in the view so they cannot be part of an index by definition
Read 10 with Order by Ascending On a calculated field - Same as the previous test but with an order by ascending
Read 10 With Order By Descending On an Index - We're going to read 10 Sales Order Headers with an order by clause in the OData query with an order by descending on a field specifically selected as being part of an index.
Read 10 With Order By Ascending on an Index - Same as the previous test but with an order by ascending
Results - Development Environment
Overall, performing an order by, ascending or descending, is faster on an index. You'll note that the performance is about the same on non-indexes fields as well as on a calculated field. The reason for this is at a SQL level, we have to get all data that would be in the set for whatever query we are performing then apply the sort. It doesn't matter to SQL if the field we are sorting on is directly on a table or a calculated field.
Results - UAT Environment
Similar to a developer environment but we see more variability most likely due to network or SQL related activity on the environment. Also, notice that calculated fields get a minor advantage.
TL;DR - Key Takeaways
- Order By on an index is faster
- Calculated fields / Sub-selects are marginally more performant than "normal" fields not in an index
All data and Power BI visuals can be found here: https://github.com/NathanClouseAX/AAXDataEntityPerfTest/tree/main/Projec...
All code can be found here: https://github.com/NathanClouseAX/AAXDataEntityPerfTest