Query failing in last two updates of SQL4CDS reporting "The given key was not present in the dictionary. See the Execution Plan tab for details of where this error occurred"

[gpriestley]

A query that we run each day that normally takes under 5 minutes has been failing in the last two versions of SQL4CDS.
It successfully runs in XrmToolBox Version: 1.2023.2.64

The query is:

update contact
set pca_emailhardbounced=dateadd(hh,10,getdate())
-- select count(contactid) from contact
-- select contactid from contact
where donotbulkemail=0 and pca_emailhardbounced is null and emailaddress1 like '%@%.%'
and contactid in
(select distinct cdi_contactid from cdi_emailevent where cdi_type = 3 AND cdi_message NOT LIKE '%mailbox full%' AND cdi_message NOT LIKE '%hop count%' and createdon>=dateadd(day,-30,getdate()))
and contactid not in
(select distinct cdi_contactid from cdi_emailevent where cdi_type in (1,2,4) and createdon>=dateadd(day,-30,getdate()))
and contactid not in
(select distinct cdi_contactid from cdi_sentemail where cdi_senton>=dateadd(day,-30,getdate()) and (cdi_deliveriescount>0 or cdi_openscount>0 or cdi_clickscount>0))

If I run the query as is in the recent versions, it will take over 90 minutes before failing with:
"The given key was not present in the dictionary. See the Execution Plan tab for details of where this error occurred"

I've tried refactoring such as "group by" instead of "distinct" in the sub queries, and this runs quicker in around 5 minutes before failing with "The given key was not present in the dictionary"

Differences in estimated execution plans attached.

---

• Deployment: Online
• DB Version: 9.2.23084.182
• Connection Controls Version: 1.2023.6.56
• XrmToolBox Version: 1.2023.9.66
• Tool Version: 7.5.2.0

Not working

Working

[gpriestley]

The file names of the graphic images aren't obvious on Github - in case you can't see the original name of the images, the one with Hash Joins is the one that work, and the one with the Merge Join and Sort aren't working.

[MarkMpn]

Thanks for the details, I’m working on a fix for this, hopefully it should be good to go next week. I’m trying to optimise the NOT IN and NOT EXISTS so hopefully this should run a lot faster as well.