Dynamics Business Central / NAV Developer Digest - Vol. 470
ArcherPoint’s Developer Digest focuses on Microsoft Dynamics 365 Business Central and Dynamics NAV development. This week’s volume includes BCTechDays 2024 workshops, potential problems when automated filtering on obsolete fields, calculating table sizes and counts, and using IncludedFields to optimize SQL queries.
The Dynamics 365 Business Central community comprises professionals devoted to advancing the success of their customers. Developers, project managers, and consultants collaborate to share helpful information across blogs, forums, and social media sites. From discovering new solutions to finding answers to complex issues, these dedicated individuals are constantly sharing their knowledge with others. At ArcherPoint, we recognize and appreciate this highly engaged community’s creativity, hard work, and collective intelligence. To ensure all users can benefit from their expertise, we want to share their wealth of information with everyone.
BCTechDays 2024 workshops are scheduled
BCTechDays is an annual two-day conference organized by MiBuSo featuring technical sessions specifically for Microsoft Dynamics 365 Business Central. This year, the event will be held June 13-14 in Antwerp, Belgium, with workshops on June 11-12. The schedule for the first workshops is available on their website. Learn more at BCTechDays.com and follow #BCTechDays on X (formerly Twitter).
Automated filtering on obsolete fields
Waldo has posed an interesting question: When using TableFilter datatype, it could very well be that after a certain upgrade, users have set this up while now they are suddenly filtering on obsoleted fields. In the case of automated functionality, users might not even realize this.
How do you go about this? What is the best way to handle this?
Check out the Obsolete Fields/TableFilters Twitter discussion.
Developer Tip of the Day: Table Sizes and Counts
Kyle H shared this useful tip: There is a page in the base product called Table Information that will count records and tell you how much database space each table is consuming. Be forewarned, it is slow, but it works on SaaS or OnPrem!
Using IncludedFields to optimize SQL queries
In this rather lengthy post, Jon L offers this insight into the use of the IncludedFields property:
IncludedFields is a property of Keys in Business Central(VSCode). There is very little documentation online if you search for IncludedFields. But I did find a blog that suggested IncludedFields allows SQL to use “Covering Indexes,” which has a ton of material online. Here’s one blog, Using Covering Indexes to Improve Query Performance. I would like to know specific scenarios where we would use this. MS uses them a lot. This would be worth investigating if you are ever facing a performance issue.
In a follow-up post, Jon posted:
This “chat” that I had with ChatGPT explains it quite well with the resultant SQL Queries towards the end of this long post.
User
Explain what IncludedFields property is on a Business Central key
ChatGPT
The term “IncludedFields” doesn’t directly correspond to a specific property in the context of Business Central keys. However, in the context of databases or key structures in general, the term “included fields” might refer to additional fields that are part of a key or index.
In a database, a key is a field or a set of fields that uniquely identifies a record in a table. Included fields, if supported by the database system, are additional fields that are stored in the index along with the key fields. Including additional fields in an index can improve query performance, especially for queries that cover the included fields.
User
Inside the key, after the {, you can set the IncludedFields property with a field, or comma separated fields. Why would you want to do this?
ChatGPT
In the context of a key in databases or systems like Microsoft Dynamics 365 Business Central, including additional fields through the “IncludedFields” property can have performance-related benefits. Here are some reasons why you might want to use the “IncludedFields” property:
Covering Queries: Including additional fields in an index can turn it into a covering index for certain queries. A covering index is one that includes all the columns needed for a query, allowing the database to satisfy the query directly from the index without the need to access the actual data table. This can significantly improve query performance.
Avoiding Table Lookups: When a query can be satisfied by the index alone, it avoids the need for additional table lookups. Table lookups involve retrieving data from the main table based on the index, and reducing these lookups can enhance query efficiency.
Reducing I/O Operations: Including frequently queried fields in the index can reduce the number of I/O operations needed to retrieve data. This is because the necessary information may already be available in the index, leading to faster data retrieval.
Sorting and Grouping Efficiency: If your queries involve sorting or grouping by certain fields, including those fields in the index can improve the efficiency of these operations.
Aggregation Performance: For queries that involve aggregations (e.g., sum, average), including the fields used in the aggregations in the index can improve the performance of these operations.
User
What is the difference between adding the field to the index and adding the field using IncludedFields?
ChatGPT
Adding a field to an index and using the IncludedFields property serve different purposes, and the key difference lies in how the index is structured and how queries are optimized.
Adding a Field to the Index:
When you add a field to an index, it becomes one of the key fields that contribute to the uniqueness of the index.
The index structure is organized based on these key fields, and queries that involve filtering or sorting based on these key fields can benefit from the index.
The indexed field is part of the key, and the index is used for direct lookups, range queries, and sorting based on that field.
Using IncludedFields Property:
The IncludedFields property is used to include additional non-key fields in the index. These fields are not part of the key that defines the uniqueness of the index.
The purpose is to cover more aspects of a query, making it a covering index for specific queries. This means that the index can satisfy the query without accessing the main data table.
Included fields are not used in the index structure itself but are stored alongside the index for better query performance.
Key Differences:
Index Structure: Fields added to the index directly contribute to the index structure and affect its uniqueness. Included fields, on the other hand, don’t impact the uniqueness of the index but are stored with it.
Query Optimization: Adding a field to the index helps optimize queries involving that field directly. Included fields optimize queries that involve those specific fields but may not be part of key-related operations.
Covering Queries: Included fields are particularly useful for covering queries, where the index contains all the necessary information for a query, reducing the need to access the main table.
In summary, adding a field to an index is about influencing the structure of the index and optimizing queries directly related to those fields, while the IncludedFields property is about improving query performance by covering additional fields without altering the key structure. The decision on whether to use one or both approaches depends on the specific query patterns and performance requirements of the application.
User
Give me an example of a specific query pattern and performance requirement for both scenarios.
ChatGPT
Certainly! Let’s consider a scenario where you have a database table storing information about products, and you often need to perform queries related to product categories and their prices.
Scenario: Product Information Database
Query Pattern 1: Filtering and Sorting by Product Category
Performance Requirement: Optimize queries that filter and sort products based on their category.
— Query without IncludedFields
SELECT ProductName, Price FROM Products WHERE Category = 'Electronics' ORDER BY Price;— Query with IncludedFields
SELECT ProductName, Price, StockAvailability FROM Products WHERE Category = 'Electronics' ORDER BY Price;Explanation:
In the first query, if the “Category” field is part of the index, it can efficiently filter products by category. Sorting is also optimized if the “Price” field is part of the index.
In the second query, using IncludedFields, you might include the “StockAvailability” field. This can help in covering the query without accessing the main table, improving performance for queries involving the selected fields.
Query Pattern 2: Aggregation by Product Category
Performance Requirement: Optimize queries that calculate the average price of products within each category.
— Query without IncludedFields
SELECT Category, AVG(Price) as AvgPrice FROM Products GROUP BY Category;— Query with IncludedFields
SELECT Category, AVG(Price) as AvgPrice, MAX(StockAvailability) as MaxAvailability FROM Products GROUP BY Category;Explanation:
In the first query, if “Category” and “Price” are part of the index, it can efficiently group and calculate the average price.
In the second query, using IncludedFields, you might include the “StockAvailability” field. This can improve the efficiency of aggregations involving the selected fields.
These examples illustrate how adding key fields to the index directly impacts query optimization for specific patterns, while using IncludedFields can help cover additional fields for different query scenarios. The choice depends on the nature of the queries you frequently perform and the performance requirements of your application.
Pranav replied: Good find Jon. SetLoadFields is another function that can be used to improve the performance for the filter (i.e. SetRange and SetFilter).
Jon answered: I use SetLoadFields all the time. I’ve tested it enough to know that it makes a huge difference in performance. Still, it’s prudent to test each case to ensure it improves performance in your particular scenario. I have seen specific scenarios which SetLoadFields decreases performance. Not sure why.
Matt T said: If you set load fields and don’t include everything you need, it has to go back to the database to get them, so two trips to the database instead of one.
Jon’s response: VS Code warns if a field is referenced and not included in the SetLoadFields. It’s trickier if the record variable is passed to a procedure.
Interested in Dynamics NAV and Business Central development? Be sure to see our collection of NAV/BC Development Blogs.
Read “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics NAV and Dynamics 365 Business Central.