I am excited about this function, but I believe I have found a limitation. Can anyone confirm or expand.

Three tables:

artist: 1200 records

artist_title (join) 7000 records

title: 7000 records

Setup:

1. FMPclient (local) --> ODBC (local) --> MySql (cloud)

2. FMPclient (all table in a single local file)

Calcs:

1. List ( artist_title::id_title )

2. ExecuteSQL ( "SELECT id_title FROM artist_title AT WHERE AT.id_artist = ?" ; "" ; ""; artist::id )

Results:

calc 1 on either setup is snappy.

calc 2, setup 2: snappy.

calc 2, setup 1: extremely slow: 1.5 minutes

I indexed the two key fields in the join table in setup1, to see if that would help but it did not.

Note: the two calcs are just for test purpose, I am just playing with the new function. To see where an how to take advantage of it.

Does anyone see a mistake, or is it because I am connecting to a MySql db. Would it perform better on a SQL server?

I don't believe the new executeSQL function is the best choice for use with the ESS feature. The ESS feature exposes mySQL tables as pseudo FM tables so that you can use them with as such. FM does the heavy lifting of translating what you do in FM to SQL and back.

So if I had to guess what you are doing triggers something like this:

ExecuteSQL code -> FM translates to FM equivalent -> goes to ESS so FM translates into relevant SQL code

In other words, I don't think your SQL is getting executed natively against the ESS source.

You could use the Execute SQL script step / Import script step from a SQL source.

Thanks Pooh-Bah! it seemed too good to be true.