At a client with about 500'000 records in the agenda - appointments table, about 20 users hitting it every 20 seconds, I was getting the UUID of a clicked appointment with
ExecuteSQL("SELECT UUID FROM AppointmentData WHERE DateID = ? AND DoctorID = ? AND StartTime <= ? AND EndTime > ? AND AgendaSide = ? FETCH FIRST ROW ONLY"
and in TopCallStats I was getting variable times ranging from 1.5 to 0.25 seconds.
I reduced this time needed by replacing the ExecuteSQL with a relationship, with a gain of 25%, but the relationship obviously had 5 conditions, out of which one ≥ and one <.
I was not satisfied and obviously angry about eSQL.
So for the fun of it, I created a calc field, startTime & "•" & EndTime & "•" & UUID and a ListOf this field, plus another relationship which returns the ListOf for the day, and instead of the ExecuteSQL oneliner or the relationship-based oneliner I do a loop on the returned ListOf (named UUIDTimeKeyList):
Set Variable [ $list ; Value: UUIDList::UUIDTimeKeyList ]
Set Variable [ $ll ; Value: ValueCount($list) ]
Set Variable [ $ind ; Value: 0 ]
Set Variable [ $result ; Value: "" ]
Set Variable [ $value ; Value: GetAsTime(IndexToTimeN($WhatTOD;$$FirstSlot)) ]
Exit Loop If [ Let($ind = $ind + 1; $ind > $ll) ]
Set Variable [ $cv ; Value: Substitute(GetValue($list; $ind);"•";¶) ]
Set Variable [ $on ; Value: GetAsTime(GetValue($cv;1)) ]
Set Variable [ $off ; Value: GetAsTime(GetValue($cv;2)) ]
If [ $value ≥ $on and $value < $off ]
Set Variable [ $result ; Value: GetValue($cv;3) ]
Exit Loop If [ 1 ]
with this subscript, which gets start time, end time and appointment UUID for the whole day, then by looping finds the record I'm searching for - or nothing => user clicked in an empty slot - I reduced the time needed to get what I want to 1/10 of the executeSQL time, i.e. from 0.4 seconds mean value to 0.04 seconds.
Sad, but worth the topic imho.
Now, we have 1'080 eXecuteSQL's in the whole solution... gotta catch'em all...