AnsweredAssumed Answered

ExecuteSQL on table with open records slows extremely down

Question asked by FileKraft on Mar 10, 2013

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.

Outcomes