[BUG] JOIN between different orgs has poor performance

[edgeerrol87]

Describe the bug
When performing INNER JOIN between two different Dataverse data sources (e.g., [UAT].dbo.contact and [PROD].dbo.contact), SQL4CDS takes 28+ minutes to process what should be a simple join.

To Reproduce
Query to reproduce the behavior:

UPDATE s  
SET parentcustomeridtype = t.parentcustomeridtype,  
parentcustomerid = t.parentcustomerid  
FROM [UAT].dbo.contact AS s  
INNER JOIN [PROD].dbo.contact AS t ON s.contactid = t.contactid  
WHERE t.parentcustomerid IS NOT NULL

Table [UAT].dbo.contact has 22,087 records
Table [PROD].dbo.contact has 22,087 records
Records matching parentcustomerid IS NOT NULL: 3,123
Query takes 28 minutes 30 seconds to execute

Expected behavior

• Cross-instance joins should be optimized to minimize data transfer
• Ideally, only matching records should be fetched from each source

Screenshots

Environment (please complete the following information):

• SQL 4 CDS edition: XrmToolBox
• Results of SELECT @@VERSION: Microsoft Dataverse - 9.2.26024.163
  SQL 4 CDS - 10.3.0.0
  Feb 04 2026 11:35:51
  Copyright © 2020 - 2025 Mark Carrington

Additional context
The UPDATE operation itself is fast (48s) once the matching records are identified, confirming that the bottleneck is purely in the cross-instance JOIN data retrieval phase.

Sponsorship
If you find this tool useful, please consider sponsoring its development.

[MarkMpn]

There is no mechanism for retrieving only the matching rows - SQL 4 CDS has to retrieve all the possible rows from each data source and then join them itself. Even so, 28 minutes seems excessive to retrieve only 44k records. It would be interesting to see the execution plan of this query after you have executed it to identify where the time was spent and the actual number of rows output by each step.

[edgeerrol87]

Hi @MarkMpn,

I was unable to reproduce the exact slow query again, but I managed to capture the execution plans from two related queries beforehand. I'm sharing them here in case they are helpful.

Query 1:

SELECT contactid FROM [PROD].dbo.contact
EXCEPT
SELECT contactid FROM [BAK].dbo.contact;

— Completed in ~4 seconds with a Hash Join (Left Anti Semi Join) between two FetchXML Queries.

Query 2:

INSERT INTO [BAK].dbo.contact (birthdate, contactid, ...)
SELECT birthdate, contactid, ...
FROM [PROD].dbo.contact
WHERE contactid IN (
    SELECT contactid FROM [PROD].dbo.contact
    EXCEPT
    SELECT contactid FROM [BAK].dbo.contact
);

— Completed in 1 minute 16 seconds, 0 rows inserted (both orgs contain ~22,000+ contact records). The bottleneck was the FetchXML Query at 70% cost (51.370s), followed by a Merge Join (Left Semi Join) at 4% (2.706s).

It seems the main cost is in the FetchXML retrieval step when doing cross-org operations. Please let me know if you need any additional information.

[MarkMpn]

Thanks - it seems the main cost here is due to retrieving a lot of columns in that Fetch XML query which made it a lot slower than the other Fetch XML queries which were just retrieving the IDs to apply the filter with.

The only other realistic query plan here would be to get just the IDs of the required records by doing the first query, then making a separate request for each of the IDs that returns. For a small number of records this may be quicker, but for your original example this would have required an additional 3,123 requests which would certainly have been slower and may well have triggered the Dataverse service protection limits. So for now I think this is the best the tool can realistically do, although it may be possible with some larger changes to have it generate a dynamic Fetch XML in query for this case.