Hi all, I have a performance question. I use an OQL to determine delete functions. If I run this query in mendix it takes more than an hour to return results. If I run the query directly on the Postgress database, it returns the 77000 records within 83ms. That is quite a difference. I also try to replace the NOT IN with NOT EXISTS, but that does not make a difference. The query is: SELECT GUID AS uniqueid FROM Interface.OrderRegel R WHERE R.GUID NOT IN (SELECT GUID FROM Interface.CheckDelete RD WHERE RD.GUID = R.GUID) I have the idea that maybe the problem is that Mendix has to create records in the non-persistent entity. Does anybody have an idea how to fix this / bump up the performance? Thanks, Jurgen
Jurgen van de Kamp
Wouldn't the query produce the same results if you omit the last WHERE statement, the one within the brackets? And then the query will run the second SELECT part only once, while now I’m guessing it gets executed for every R record to compare GUIDs.
Have you tried a left outer join:
SELECT GUID AS uniqueid
FROM Interface.OrderRegel R
LEFT OUTER JOIN Interface.CheckDelete RD ON R.GUID = RD.GUID
WHERE RD.GUID IS NULL