I'm putting a "spotlight search" field on a layout. The layout is based on a table of Sessions, related to Programmes, which is related to Programme Titles. The search will eventually be on several fields - but for testing, I'm just using the "title" field in Programme Titles. It's a text field, indexed.
I'm hoping to use Execute SQL function, and I've got it working, but very slowly. To do the query on 24,000 (approx) Session records, it's taking 8 seconds, which is a lot slower than I hoped for. Or am I being over-optimistic about what I hoped for? (An equivalent Filemaker Find is a bit quicker, but not much - 10% quicker on average).
This is the ExecuteSQL function that I'm using:
ExecuteSQL("SELECT S.id FROM SSN s, PGM p, PTI t
WHERE s.id_PGM = p.id
AND p.id_PTI = t.id
AND LOWER(t.title) LIKE ? " ; "" ; ""; SSN::FILTERINPUT & "%" )
(where SSN, PGM and PTI are the Sessions, Programmes and Programme Titles tables, and SSN::FILTERINPUT is the global field in which the user enters the search string.)
I would love to be told that I'm doing something wrong (I'm still on the ExecuteSQL learning curve).