deninger

ExecuteSQL is very slow with ESS data

Discussion created by deninger on Oct 9, 2013
Latest reply on Oct 22, 2013 by LSNOVER

Maybe this is not news to everyone here, or maybe my setup is very sub-optimal, or maybe I am missing something significant but...

 

I was very excited to implement ExecuteSQL Function in some of my solutions. My thought was that I could quickly grab data from a table with SQL instead of switching to a Filemaker Layout, scripting a search, and returning the value I was interseted in.

 

In practice, however, a simple call like:

 

Let (

[

$dispenseID = $someValue; // passed to the step from earlier

$sql = "SELECT ITEM.NDC_CODE FROM ITEM, DISPENSE

WHERE DISPENSE.DISPENSED_ITEM_ID = ITEM.ITEM_ID

AND DISPENSE.DISPENSE_ID = " & $someValue

$result = ExecuteSQL ($sql ; "" ; "" )

];

 

 

$result

)

 

 

is much, much slower than switching to the Item layout and searching for the dispense ID, returning to the original layout and returning the needed value.

 

Is the problem related to the fact that the data being accessed by the ExecuteSQL is actually an ESS datasource instead of a native FM table?

Outcomes