2 Replies Latest reply on Sep 17, 2014 6:12 AM by firebase

    SQL in Portal Filter to slow

    firebase

      Title

      SQL in Portal Filter to slow

      Post

      I am using this request:

      ExecuteSQL ("
      SELECT tbl1.name
      FROM tbl1, tbl2
      WHERE tbl1.name = ?
      AND tbl2.number = ?
      FETCH FIRST 1 ROW ONLY
      " ;"";""; tbl3::name; tbl4::number)

      in the Portal row filter, but its just to slow for me. It was ok untill i added the 2. table in the FROm part, but after that it takes about 10 seconds to load every new page.

      Is there any way to speed this up? Is there a way to do it in regular FM functions? If so would it be faster?

        • 1. Re: SQL in Portal Filter to slow
          philmodjunk

          Looks like you need a Join clause defining the relationship between tbl1 and tbl2. As written, there's no defined link between the two tables.

          There are two basic ways to speed up a portal filter:

          Change the relationship so that the set of related records to be filtered is smaller. An expression you define must be evaluated once for every related record so if you use a cartesian join operator to match to all portal records you could be setting up FileMaker to evaluate this expression a great many times.

          Make the calculation itself more efficient. (That's where my noting the lack of a Join clause comes in here.) An inefficient SQL query or a looping recursive calculation both could greatly increase the calculation "Load" on layout updates.

          • 2. Re: SQL in Portal Filter to slow
            firebase

            Ooops i was missing the join part in my example :)

            Thanx for the hint for prefiltering on relational area, thats where i not thought about.