After several recent updates to Dataverse I’m excited to release SQL 4 CDS 5.2 to take full advantage of the new platform features, along with several UI and query handling improvements.

Bulk INSERT/UPDATE/DELETE Performance

INSERT and UPDATE are some of the most common queries run in SQL 4 CDS. These can now take advantage of two new options introduced in the Dataverse platform.

The first is the option to spread the load of bulk operations across multiple servers. This is handled automatically for you whenever SQL 4 CDS detects you’ll be inserting, updating or deleting more than 100 records and using multiple threads.

To get the best out of this, please use queries that affect multiple rows in one query, e.g. run:

UPDATE account
SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb',
owneridtype = 'systemuser'
WHERE industrycodename = 'Accounting'

rather than the pattern I often see which is to run a query like:

SELECT accountid
FROM account
WHERE industrycodename = 'Accounting'

then manipulate the results in Excel to generate pages of individual UPDATE queries:

UPDATE account SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb', owneridtype = 'systemuser' WHERE accountid = 'abe98c67-91d8-4b17-af4d-f7dc6baddef1'
UPDATE account SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb', owneridtype = 'systemuser' WHERE accountid = 'c1647fac-b1c1-46ae-b2e1-a289a4375acb'
UPDATE account SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb', owneridtype = 'systemuser' WHERE accountid = '3bbd3be4-cbf6-46e7-9383-f8bb91c6f217'
UPDATE account SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb', owneridtype = 'systemuser' WHERE accountid = '60fe7ce9-a894-4847-99f9-1b76dc0d5d0e'
UPDATE account SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb', owneridtype = 'systemuser' WHERE accountid = '54bfbc89-fffc-44cc-9505-24a7ec7c4b63'

as these queries will be run sequentially and won’t be able to take advantage of this optimisation.

Bypass Plugins

If you’re tidying up data in some way you may want to skip any plugins in your system.

You can now do this using the new “Bypass custom plugins” option in the Settings dialog:

Remember to turn it back on when you’re finished!

If you have this option turned on and don’t have permission to bypass plugins you’ll get an error.

Remembering your tabs

By default SQL 4 CDS will keep your tabs open when you re-open it so you can keep your queries across sessions. If you prefer you can turn this off in the Settings dialog:

Changing connection

If you have a query that you want to run on multiple instances, you used to have to create a new tab for each connection and copy & paste your query across. You can now use the new Change Connection button in the toolbar to switch the instance the current tab is connected to:

Accessing FetchXML

To get the FetchXML for your query you can double-click on the FetchXML Scan node in the execution plan:

This used to take you straight to FetchXML Builder, which can lead to an annoying delay if you just need the raw FetchXML. This now opens the FetchXML as a new tab in SQL 4 CDS itself:

From here you can click the FetchXML Builder button to edit it in FXB, or just copy & paste it to where you need it. The XML uses single quotes so you can paste it into your C# plugin code easily.

Reordering joins

You may now notice that the generated FetchXML uses a different arrangement of <entity> and <link-entity> elements than you’d expect from the order of your joins. This is because SQL 4 CDS will now re-order them automatically where possible to avoid some of the paging limitations I’ve blogged about earlier which could lead to your query missing some rows.

Minor UI changes

The “Connect” button on the toolbar is now in the Object Explorer pane:

From here you can add new connections to the Object Explorer, or remove existing connections.

You might notice the same icon in the main toolbar too. This is used to connect the current query tab to a Dataverse instance if it is not currently connected.

I’ve also added a number of help links to the Settings dialog which will take you to the relevant page in the Microsoft documentation that describes that option in more detail.