ExecuteSQL INs : adding 1 criterion, slows down 81 times !

Discussion created by Vincent_L on Oct 15, 2018
Latest reply on Oct 16, 2018 by siplus


Unfortunately, most ExecuteSQL users know that the IN function can make queries very slow.

But today I discovered that it's not so much the IN itself that is slow but having another criterion in the WHERE condition that absolutely kills performance (81x slower).


I always thought that it was logical to expect that


A. SELECT id FROM MyTable WHERE ids IN (123,678,8769,8990) AND status=1


would be faster than


B. SELECT id FROM MyTable WHERE ids IN (123,678,8769,8990)


Provided only few ids have a status of 1, and everything is indexed since the number of the matching ids would much lower.


It turns out that B is way faster, 81x time faster than A. That's totally unexpected to me.


Given the fact that in the most real-world usage, you certainly combine the IN with other criteria, the INs bad performance reputation could come in fact to that added criterion slowness.


That's quite a shock to me, and I think the community should know about this, as it may be a route to better optimize ExecuteSQL.


Don't get me wrong, I think that's highly problematic and that FMI should address this ASAP.

I know that Filemaker is not SQL, and that FileMaker "SQL" queries are translated to Find queries. But even with that consideration, this is not logic, as finds get faster with more indexed criteria (if they help to narrow down record count).


You'll find attached (admin/blank) a file that demonstrates this.

The first record is query A like, second record is query B like.


Something that's also strange is that usually, ExecuteSQL queries are faster on the second try, not in that case for query A like.