A difficulty has arisen with my inquiry. I've provided a basic example to demonstrate the code I have.
SELECT distinct ID FROM TableWHERE IteamNumber in (132,434,675) AND Year(DateCreated) = 2019AND ID NOT IN ( SELECT Distinct ID FROM TableWHERE IteamNumber in (132,434,675) AND DateCreated < '2019-01-01')
To be clear, I'm only retrieving data IDs that were generated in 2019 and later.
It's not that the select statements don't work; it's just that when I add the NOT IN statement, the query can take up to an hour.
Moreover, could this be related to the speed of the computer or server on which SQL Server for Microsoft Business Central is installed? After all, the same query executed without issue in Microsoft dynamics C5 SQL Server despite including a (NOT IN) clause.
So, my inquiry is, is there something wrong with my query, or is this primarily a server issue?
UPDATE: here is a real example: this takes 25 seconds to retrieve 500 rows
Select count(distinct b.No_),'2014'from [Line] c inner join [Header] aon a.CollectionNo = c.CollectionNoInner join [Customer] bon b.No_ = a.CustomerNowhere c.No_ in('2101','2102','2103','2104','2105')and year(Enrollmentdate)= 2014and(a.Resignationdate < '1754-01-01 00:00:00.000' OR a.Resignationdate >= '2014-12-31')and NOT EXISTS(Select distinct x.No_ from [Line] c inner join [Header] a on a.CollectionNo = c.CollectionNo Inner join [Customer] x on x.No_ = a.CustomerNo where x.No_ = b.No_ and c.No_ in('2101','2102','2103','2104','2105') and Enrollmentdate < '2014-01-01'and(a.Resignationdate < '1754-01-01 00:00:00.000' OR a.Resignationdate > '2014-12-31'))