0 Replies Latest reply on Mar 10, 2013 2:59 PM by FileKraft

    ExecuteSQL on table with open records slows extremely down

    FileKraft

      Summary

      ExecuteSQL on table with open records slows extremely down

      Product

      FileMaker Pro

      Version

      12v3

      Operating system version

      OSX 10.8.2

      Description of the issue

      i use some simple ExecuteSQL function to get some data - the data comes from a table with uncommitted records. the function works fine just extremely slow - if there is at least one record open of that table. as soon as i commit the function evaluates immediately.

      now i tried to replicate the problem - creating a new file - and i cannot replicate the slowness. there is no difference in speed - committed or uncommitted. the new file is of course smaller - i entered the same amount of records but the rest of the file has nothing in there - no triggers almost no scripts and it is a just a lean creation trying to demo the issue i am facing.

      ( if i try some recursive home made custom functions to fetch the same data - there is no difference in speed if records committed or not .. )

      Steps to reproduce the problem

      use a complex file - write a valid SQL function over some data of a table with open records and check the speed of execution.

      Expected result

      no difference in speed to execute function on set of all committed or some uncommitted.

      Actual result

      very slow execution of ExecuteSQL function - takes a minute vers. a fraction of a second

      Exact text of any error message(s) that appear

      data viewer shows sand watch.

      Configuration information

      the index of the file is ok - no recovery log indicates otherwise - file very stable - no crashes ..

      Workaround

      using a recursive custom function (with its stack limitation of 50.000 tailrecursive calls) to fetch the desired data .. instead of the slow ExecuteSQL function.