Hi Martin,
I will try to reproduce it with standard tables and let you know. But not sure what would be special about those tables that would make it different. (please note that those two tables are custom tables but not in my model)
but just to add
Exists join at first resulted in worse performance.
With inner join:
In trace parser, the call tree showed inclusive(ms) =588.65 for the query below
Now after i changed it to
exists join:
In trace parser ,the call tree showed inclusive(ms) =1291,37 for the query below
I'm not sure why it said SELECT '
X' FROM XXXPARAMETERS T2 (maybe because i
didn't put any fields in the query for the parameters table)
SELECT T1.XXXID,
T1.RECVERSION,
T1.RECID
FROM AMRENTTABLE T1
WHERE (((T1.PARTITION=5637144576)
AND (T1.DATAAREAID=N'XXX'))
AND ((T1.CREATEDDATETIME>='2025-12-7')
AND (T1.CREATEDDATETIME<='2025-12-8')))
AND
EXISTS (
SELECT 'X'
FROM XXXPARAMETERS T2
WHERE (((T2.PARTITION=5637144576)
AND (T2.DATAAREAID=N'XXX'))
AND (
NOT ((T2.XXXLOCATIONID=''''))
AND (T1.XXXLOCATIONID=T2.XXXLOCATIONID))))
ORDER BY T1.XXXID
then i repeated it again with exists join, but this time i didn't leave fields empty in the query for XXXParameters table, i
added one field which is XXXLocationId to the query.
Now in trace parser, the call tree showed inclusive(ms) =371.94
but the query still showed select 'X' from XXXParameters, not sure why
So does that mean i need to use exists join, and
select a field in the query from XXXParameters table and
not leave it with empty fields?
