siplus

Avoid searching for > in relationships or eSQL in "big" tables

Discussion created by siplus on Oct 15, 2018
Latest reply on Oct 17, 2018 by Ben

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)) ]

Loop

  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 ]

  End If

End Loop

 

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...

Outcomes