Benchmark Analysis: Calculated vs Formula Column in Dataverse¬¬¬¬”¯¯¬
Dive deep into Dataverses Calculated vs Formula Column. Discover the performance dynamics and pick the right one for your needs!
In the realm of Microsoft Dataverse, a variety of Data Types are available to users, and among them, Calculated and Formula Columns stand out due to their functional similarities. The well-established Calculated Columns are now joined by an intriguing new preview feature known as Formula Columns, offering an alternative method for creating calculated values. The real question that captures the interest of many is the performance comparison between these two types of columns.
The experiment began with one table encompassing two main attributes: Quantity (Number) and Price (Money). A Calculated Column and a Formula Column were both created, with a key distinction in their Data Types – Money for Calculated Columns and Decimal for Formula Columns.
After updating to include Price 2 (Decimal), further comparisons were made between additional Calculated Columns and Formula Columns. To benchmark, 10 records were used to test the performance of each column type.
Benchmarking involved a .NET 6 Console application relying on Nuget Packages linked with Microsoft Power Platform's Dataverse. Using BenchmarkDotNet, detailed code outlines the methods for data retrieval, showcasing the comprehensive testing regime undertaken.
The benchmarking code offers six methods for data retrieval, varying in Data Type combinations and using a fresh instance of IOrganizationService each time. To ensure valid results, the typical Dataverse caching mechanisms were bypassed by deactivating the warm-up functionality in the code.
The conclusion of the benchmarking code highlights that a command in the terminal, 'dotnet run -c Release', must be executed to carry out the performance test. The results underscore the subtle differences in efficiency between using Calculated Columns and Formula Columns.
BenchmarkDotNet provided the results, including standard deviation, median, mean, error rate, and memory allocated per operation. After the alterations, the Calculated Column demonstrated better performance in both scenarios. Nonetheless, the versatility of the Formula Column might be more suitable for complex calculations.
This deep dive into the intricacies of Microsoft Dataverse underscores the importance of selecting the right type of column based on specific requirements and the impact on performance. Choosing between Calculated and Formula Columns is a key consideration for users who require precision and optimization in their database solutions.
A Closer Look at Microsoft Dataverse Columns
Exploring Microsoft Dataverse, we delve into the significant functionalities and performance metrics of Calculated Columns and Formula Columns. The study of these Data Types reveals nuances in operational efficiencies, impacting the choice of users tailoring their database strategies. Emphasizing a pragmatic approach, the blog post by Citizen Developer evaluates these columns through a pragmatic lens, presenting evidence-based conclusions beneficial for Microsoft Dataverse developers and enthusiasts aiming for optimized solutions.
Decoding Dataverse: Calculated Column vs Formula Column
In Microsoft's cloud offering, Dataverse, a variety of Data Types are available. One comparative study focuses on two seemingly similar functionality Data Types, leaving the Roll-up Columns untouched due to their independent refresh cycle.
The established Calculated Columns, together with the recent addition, Formula Columns, undergo a performance head-to-head. The crux revolves around the performance differentiation between these two entities.
To set the scene, a table is populated with two significant attributes: Quantity, a numeric data type, and Price, a monetary data type. Then, a representation of each Column type is created - the Calculated Column and the Formula Column. There are slight differences in the application's data type.
To suggest a fair match, certain additional attributes are incorporated. Post definition of Metadata, ten records are created to facilitate the benchmarking process.
For the technical crowd, the benchmarking method employs a .NET 6 Console app, largely reliant on specific NuGet packages. Using BenchmarkDotNet and some fine-tuned code, the process sets onto benchmarking.
Six methods are leveraged to retrieve data, considering the data type combinations. Moreover, the data retrieval follows the same query, which by default, Dataverse caches. Hence, the code ensures a new instance of IOrganizationService is created without warm-up for each method pursued.
Running the benchmarking then unveils a performance report dispatching the average of all extents. Here, after a few rounds of updates, Calculated Columns show minute performance superiority. Though, versatility credit goes to the Formula Columns as it supports more complex scenarios.
Thus, while Dataverse's Calculated Columns are still relevant, the new Formula Columns prove to be a dynamic addition, paving way for more adaptable and innovative use cases in Microsoft's cloud ecosystem.
Keywords
Dataverse Benchmark, Calculated Column, Formula Column, Dataverse Calculated vs Formula, Data Column Comparison, Dataverse Column Analysis, Formula Column Benchmark, Benchmarking Columns, Data Analysis Benchmark, Column Calculation Evaluation