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.
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)
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.
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.
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
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'
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.