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:

 

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

 

Dave.

Outcomes