This update focuses mainly on aggregate query performance and introduces .NET Core compatibility for developers wanting to integrate SQL 4 CDS with their own applications.
One type of query I commonly want to run is an aggregate query – this is any query using functions such as COUNT, SUM, MIN, MAX etc, or GROUP BY.
Dynamics 365 has a few restrictions on this though. In particular, if the query aggregates more than 50,000 records it will generate an error.
SQL 4 CDS has had some alternative methods of running these queries for some time already. This update introduces two more optimisations to help run these queries faster on larger data sets.
To find the number of active accounts you can use the query
SELECT count(*) FROM account WHERE statecode = 0;
This generates the following query plan, which has three different attempts to run the query. Each attempt is processed in order from top to bottom until one runs without error.
If the aggregate query can be converted to FetchXML, SQL 4 CDS will first try to run it as normal. If it hits the 50,000 limit it will then try a new partitioning strategy.
This breaks the data down into half based on the createdon date. The query is then run on each half; if it still hits the 50,000 limit it is halved again until the query runs successfully. The aggregates for each section are then added together to get the final results.
This should work in most cases, but some tables don’t have a createdon field to use for partitioning. It might also fail if the data was loaded over a very short time span so it can’t effectively be split by the createdon field.
If partitioning isn’t possible, SQL 4 CDS will then retrieve all the individual records and calculate the aggregate values itself. This update introduces the Stream Aggregate operator which does this more efficiently than the previous Hash Match Aggregate, and you’ll see this used wherever there are either no GROUP BY fields, or the data can be sorted by those fields.
If for any reason you want to use the Hash Match Aggregate operator you can apply the OPTION (HASH GROUP) query option.
This update introduces a new drop down list in the toolbar to let you quickly switch to running your query against a different D365 instance.
Click the icon to add more instances to the Object Explorer pane and they’ll be added to this list too. Select an instance from this list and the current query window will use it the next time you run the query.
SQL 4 CDS has long supported converting FetchXML queries to SQL, which you can use from FetchXML Builder.
In this release this is enhanced with the option to use the conversion used behind the scenes by Dynamics 365 itself. This typically gives a more complex SQL query but includes things like the name fields for related lookup values.
On the Settings screen, go to the Conversion tab and select which version you prefer:
In FetchXML Builder, open the Options page and ensure “Use SQL 4 CDS for SQL conversion” is checked. Now when you click View > SQL you’ll see the query converted using the format you’ve selected. For example, the FetchXML query
<fetch aggregate="true"> <entity name="account"> <attribute name="accountid" alias="count" aggregate="count" /> <filter> <condition attribute="statecode" operator="eq" value="0" /> </filter> </entity> </fetch>
is converted to either the simple SQL query
SELECT count(accountid) AS count FROM account WHERE statecode = 0;
or the more complex
select COUNT(*) as "count" , MAX("__AggLimitExceededFlag__") as "__AggregateLimitExceeded__" from (select top 50001 case when ROW_NUMBER() over(order by (SELECT 1)) > 50000 then 1 else 0 end as "__AggLimitExceededFlag__" from Account as "account0" where ("account0".statecode = 0)) as IQ order by __AggregateLimitExceeded__ DESC
In this second version you can see the 50,000 record limit that is introduced by Dynamics 365.
You can now reuse the SQL 4 CDS library in your own .NET Core 3.1 or later applications. This library uses the newer DataverseClient.
The same NuGet package works in .NET Framework as well, but uses the older CrmServiceClient.
There is more information and sample code available to get started with the library on the NuGet page.
This release also includes a number of fixes for specific queries I’ve seen problems with:
- filtering on partylist fields (e.g. email.to)
- query-derived tables with aggregates
- functions in WHERE clause that can’t be converted to FetchXML
- multi-select picklist fields in ORDER BY and GROUP BY
- LIKE with non-string fields
- more than 10 joins
- using TOP N WITH TIES
- retrieving file columns
- using quoted identifiers
There is also a new option on the Editor tab of the Settings screen to show datetime values in your local format rather than ISO standard format.