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.

Partitioned Aggregate

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.

Stream Aggregate

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.

Switching Instances

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.

FetchXML to SQL Conversion

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.

.NET Core Compatibility

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.

Other fixes and improvements

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.

8 thoughts on “SQL 4 CDS 5.4 Released”

  1. Hello Mark

    It appears that the SQL 4 CDS 5.4 version is broken when using aggregation.
    This query in previous version of SQL 4 CDS was working fine.

    SELECT incidentid, knowledgearticleid, COUNT(*)
    FROM knowledgearticleincident
    –Where StatusCode=1
    GROUP BY incidentid, knowledgearticleid
    HAVING COUNT(*) > 1

    But the 5.4 version returns erroneous results…when I revert to the old version the query works correctly.

    The results from 5.4 seem to be not grouping properly by what appears the second group by clause … in my case “knowledgearticleid” … I have an incidentid that has two knowledgearticleid but they are different but counts them as the same

      1. I’d be happy to share but I am not able to copy (Ctrl-C) paste (Ctrl-V) a screenshot into the blog. Could you email me and I’d be happy to provide via that route. Would you want the FetchXML provided by the SQL 4 CDS tool?

        1. Could the “Use TDS Endpoint where possible (Preview)” option in “Settings” have any affect from 5.3 to 5.4? I put 5.4 back and with the above option set the query appears to be returning what I would expect … if I uncheck and rerun I get erroneous results.

  2. Hi Mark,

    thank you for introducing the option to show datetime values in local format

    Now copying to excel with german locale is working again 🙂

    Regards
    Olaf

  3. Hi Mark,

    you wrote under improvements “more than 10 joins”

    I just gave this a try by specifying 12 joins and got this error message:
    Number of link entity: 11 exceed limit 10. isReport=False.
    See the Execution Plan tab for details of where this error occurred

    Do I have to take something else into account?

    Regards
    Olaf

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.