MySQL Mirror vs PHP

[ThePablog]

Hello,

 

I have need to make many calls to our FM DB from our BI software using ODBC/JDBC. I have successfully connected to FM using ODBC, but as we all know, its uber slow. So, after extensive googlin' I've found two good options for our needs; FxPHP or ESS. 

 

I've been reading this post about ESS and how I can script a replication between all the FM tables to a MySQL instance. But this seems like a big job. At the end of the day, all I want to do is be able to query or FM data without having to wait an hour for the data to return. 

 

Could some recommend to either:

• Use FxPHP to make calls to FM directly

OR

• Make a MySQL database and use ESS on FM to copy it self onto it, then query that MySQL DB. (shadow vs replication?)

Any advice would be appreciated.

 

Thanks.

[Wim Decorte]

FxPHP is just one API, you can also use FileMaker's native PHP Api,

or use one of the replacement APIs like fmDotNet, Rfm (ruby),...

 

There's really no good comparison between using the various APIs that use the web publishing engine and using ESS.  To very different beasts.

If you are proficient with PHP, then that's a good option.  If not, you may find that pushing data to an ESS table is easier to put together and maintain.

 

Having said that: ODBC is not that slow normally.  What kind of query do you make and how much data are you returning?

If your underlying design in FM is not efficient (as in: lots of unstored calcs and/or summary fields in the fields that you are querying) then everything will always be slow, no matter if you touch the data through a web publishing API or pushing the data into an ESS table.

[ThePablog]

Thanks Wim, 

 

A select * from a table of 2.5k rows took 48 minutes, this was running the FM DB locally on the same PC running the query using the FM ODBC driver (on an [email protected], 16GB RAM and mirrored SSDs)    But as you say, a whole bunch of those fields are calculated and unstored. This is necessary for most of those fields because of the nature of the data and it running on anchor buoy.

 

But if you say that this is going to be slow no matter if I use PHP or ESS, then I perhaps need to make copies of the tables but with less fields, store those fields and minimal relationships... tables made for the purpose of being queried ans stick to vanilla ESS.

 

Thoughts?

 

Ta.

[Wim Decorte]

Anchor/Buoy has nothing to do with fields needed to be unstored, nor does the nature of the data.

 

Typically most unstored calc fields can be set through a script workflow.... fundamentally that is a better solution than trying to duplicate data just to make queries work.