I’ve just published SQL 4 CDS 6.4 with support for earlier versions of CRM and more complex queries. I’ve also been setting up a wiki with more detailed information.
Wiki Documentation
I’ve set up a wiki on the GitHub site to include some much more detailed documentation about the supported SQL syntax and how to use more of the advanced features of SQL 4 CDS to query and modify your data in Dataverse / Dynamics 365. It’s a work in progress and I’d love your feedback on what you’d like to see to make working with SQL 4 CDS easier.
On Premise Dynamics CRM Support
SQL 4 CDS has always supported on-premise instances, but this release extends the versions it works with. It should now work with all versions from Dynamics CRM 2011 onwards!
I don’t have all versions available to test with so if you do encounter any errors connecting to earlier versions please let me know.
Subquery Improvements
Daryl LaBar posted recently showing how to quickly enable or disable plugins in bulk using SQL 4 CDS. The query he showed was:
UPDATE sdkmessageprocessingstep SET statecode = 0, statuscode = 1 WHERE sdkmessageprocessingstepid IN (SELECT sdkmessageprocessingstepid FROM sdkmessageprocessingstep WHERE plugintypeid IN (SELECT plugintypeid FROM plugintype WHERE pluginassemblyid = '95858c14-e3c9-4ef9-b0ef-0a2c255ea6df') AND statecode = 1);
This works fine, but exposed an inefficiency in how SQL 4 CDS processes the nested IN
subqueries. This update can now convert this to a single FetchXML query for faster processing.
While I’m on the topic I’d also like to offer this simplified query to do the same job based on the name of the name of the plugin assembly so you don’t have to find the guid:
UPDATE sdkmessageprocessingstep SET statecode = 0, statuscode = 1 FROM sdkmessageprocessingstep INNER JOIN plugintype ON sdkmessageprocessingstep.plugintypeid = plugintype.plugintypeid INNER JOIN pluginassembly ON plugintype.pluginassemblyid = pluginassembly.pluginassemblyid WHERE pluginassembly.name = 'MarkCarrington.Dev.Plugins' AND sdkmessageprocessingstep.statecode = 1
This update also fixes “ambiguous column” errors when using IN
and EXISTS
subqueries, and an error that could result in multiple rows being returned incorrectly when using a scalar subquery. This fixes queries like:
SELECT name, (SELECT TOP 1 fullname FROM contact WHERE parentcustomerid = account.accountid) FROM account
Join Performance Improvements
The query optimizer will now select a more efficient merge join rather than a hash join for more queries. Joins which can’t use either of these join types will use a nested loop, and these are also improved by caching the data in the inner loop to give a massive performance boost to these queries.
Aggregate Queries
Some aggregate queries could fail with an “Object reference not set to an instance of an object” error, especially if MIN
or MAX
was being used and all the values in a group were NULL
. These queries will now produce the correct values.