SQL 4 CDS v7.6 Released

It hasn’t been long since the previous release, but there’s some important fixes in this version along with some useful new features and performance improvements.

Fixes for non-FetchXML joins

There were a few bugs in the last few updates to do with the way SQL 4 CDS was handling joins that it couldn’t convert into FetchXML. This caused error messages like:

The given key was not present in the dictionary (#359)

or

Object reference not set to an instance of an object (#364)

when running the query. If you were joining tables in the metadata schema and added additional filters within the ON clause, it could also result in those filters being lost and you’d get back more data than expected (#360).

Many thanks to everyone who reported these errors on GitHub. The sample queries and execution plans really helped diagnose the problems.

NOT IN/NOT EXISTS Improvements

One of the example queries for the join errors made extensive use of NOT IN filters with subqueries. As well as fixing the original problem, I’ve also updated how the query optimizer works for these cases.

Consider this simple query:

SELECT name
FROM   account
WHERE  ownerid NOT IN (SELECT systemuserid
                       FROM   systemuser
                       WHERE  firstname = 'Mark')

In previous versions this would load in a list of all accounts, and a list of all users with a first name of “Mark”, then join the two together to find the accounts that didn’t match those users:

This works (except when it triggered one of the bugs mentioned above with the join), but isn’t very efficient.

With the optimizer change in this version, SQL 4 CDS converts this to a single FetchXML query. Dataverse does all the processing more efficiently within the back-end SQL database and only returns the matching rows:

Behind the scenes, SQL 4 CDS is now rewriting this as a LEFT OUTER JOIN and filtering out the records that match the related table:

<fetch xmlns:generator='MarkMpn.SQL4CDS'>
  <entity name='account'>
    <attribute name='name' />
    <link-entity name='systemuser' to='ownerid' from='systemuserid' alias='Expr1' link-type='outer'>
      <filter>
        <condition attribute='firstname' operator='eq' value='Mark' />
      </filter>
    </link-entity>
    <filter>
      <condition attribute='systemuserid' entityname='Expr1' operator='null' />
    </filter>
  </entity>
</fetch>

Depending on your data you could see a huge improvement in query performance with this change.

UPDATE support for many-to-many intersect tables

Many-to-many relationships (such as contacts being members of multiple marketing lists) are implemented through an intersect table:

You can’t create, update or delete records in these intersect tables in the same way as normal tables. The records are created automatically by the Associate message, and deleted using the Disassociate message. (Marketing list members are special and are managed by the AddMemberList and RemoveMemberList messages instead).

SQL 4 CDS has long supported the INSERT and DELETE statements for these tables by using these alternative underlying messages, but trying to UPDATE has triggered the error:

Cannot update many-to-many intersect entities:
DELETE any unwanted records and then INSERT the correct values instead

Thanks to a little nudge from Daryl LaBar I’ve now removed this restriction. Although Dataverse doesn’t offer a message to update these records directly, SQL 4 CDS emulates it by simply removing the existing record and adding the new one. The two operations are combined in a transaction so if one fails then the other is automatically rolled back.

You can now use a query like this to selectively move members from one marketing list to another:

UPDATE listmember
SET    listid = (SELECT listid
                 FROM   list
                 WHERE  listname = 'New List')
FROM   listmember
       INNER JOIN
       contact
       ON listmember.entityid = contact.contactid
       INNER JOIN
       list
       ON listmember.listid = list.listid
WHERE  list.listname = 'Old List'
       AND contact.firstname = 'Move'

Sorting on Audit and Elastic Tables

I’ve come across a few differences with how sorting is applied to the Audit table and other elastic tables:

• Multiple sorts don’t work. If you try to sort an elastic table by two different fields you’ll get an error The order by query does not have a corresponding composite index that it can be served from. You don’t get this error on the audit table, but the second sort seems to be silently ignored instead.
• Picklist/choice/optionset fields. Normally, if you sort by one of these fields the results get returned in the order of the display name. For audit & elastic tables they’re sorted by the underlying number value instead.
• Lookup & primary key fields. Standard tables sort these using the SQL Server rules for comparing guid values, but elastic tables compare them as strings instead.
• Sorting on certain fields in the audit table has no effect, such as the attributemask and changedata fields.

This version improves SQL 4 CDS’s understanding of what sorts are safe to get Dataverse to execute as part of the FetchXML query, and which ones it should do internally.