AnsweredAssumed Answered

Execute SQL function EXTREMELY slow - hangs and crashes

Question asked by dinoapolito on May 19, 2014
Latest reply on May 23, 2014 by TSGal

Summary

Execute SQL function EXTREMELY slow - hangs and crashes

Product

FileMaker Pro

Version

12 Advanced

Operating system version

Windows 8.1

Description of the issue

I have some tables where I have added a calculation field that runs an execute sql function to other tables. Like this

ExecuteSQL (


"select ProductCode, copies from ordercomponents

where  orderitemid=?
"
; " x" ; ", ";OrderItemID )

This all works brilliantly on my test database which only has a few thousand records but when I use it on a customer database it falls down in a screaming heap. The FROM table in the customer database (ordercomponents) has over 1 million records in it.

As soon as I load a layout that has a value for this field Filemaker hangs, and is non responsive. I have waited 20 minutes before killing the process.

Is there anything I can do or is this beyond Filemaker?

The tables themselves are shadow tables from an external database. The equivalent query elsewhere directly on the external database is very fast.

While Filemaker was hung I ran MySQL Workbench and executed a SHOW PROCESSLIST to see what was happening. It is bizarre. It was sending the followoing query

"Select ordercomponentid from orders where ordercomponentid>1" and it was doing this for every orcercomponentID eg

"Select ordercomponentid from orders where ordercomponentid>2"
"Select ordercomponentid from orders where ordercomponentid>3"

Why would Filemaker be asking MySQL to do this?. There are 1.5 million records in that table

Hope someone can help

Dino

Steps to reproduce the problem

A calculation field on a shadow table (orders) is using an execute sql function to query a related shadow table (ordercompnents).

The two tables are related v1a an orderid

The orders table has 500,000 records
The ordercomponents table has 1.5 million

Expected result

For each orderid in the orders table I expect a result from the ordercomponent table

Actual result

It hangs on large databases and seems to be sending a stupid query to the server

Outcomes