AnsweredAssumed Answered

ExecuteSQL is slow - or is it me?

Question asked by davehob on Nov 18, 2013
Latest reply on Jan 14, 2014 by user19752

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:



WHERE s.id_PGM =

AND p.id_PTI =

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