SQL 4 CDS 6.3 Released

I’ve just released SQL 4 CDS v6.3 with a number of useful improvements and fixes.

INSERT/UPDATE/DELETE performance improvements

Bulk data modifications are now able to be performed in parallel if you use client secret or certificate authenticate connections as well as OAuth username & password. This can give a huge performance benefit when you use these authentication types.

Multi-table improvements

Rather than using standard join syntax, it’s also possible to execute a query across multiple tables using syntax such as:

SELECT account.name,
       contact.fullname
FROM   account,
       contact
WHERE  account.accountid = contact.parentcustomerid

In this update, this query is automatically converted to the equivalent join to produce an efficient FetchXML query. The Intellisense suggestions are also improved for this syntax.

Join performance improvements

When a large number of joins are used in a query, it could take several seconds to convert it to FetchXML. This version massively speeds up this process so you can execute queries with multiple joins much quicker.

Query hint improvements

Query hints give you a lot of power to control individual queries by bypassing plugins and other custom features, but it’s easy to misspell them. This update provides validation of the hint names as well as removing any bespoke SQL 4 CDS hints before executing a query against the TDS endpoint.

Audit record deletion

In online instances, audit records can’t be deleted individually but can be removed using a bulk delete job. This update automatically converts a DELETE FROM audit ... query to a bulk delete job regardless of the “Use bulk delete operations” setting.

FetchXML improvements

The FetchXML generated by this version is easier to read and edit in FetchXML Builder as it removes the unnecessary nesting of filters. It also fixes a bug with the automatic page size calculation so large result sets should be retrieved faster.