[FX.php List] FM Scripts vs. PHP

[Derrick Fogle]

I've done a LOT of work with PHP - talking both to MySQL and to FM  
(versions 6, 7, and 8) via FX.php, even using PHP as a data migration  
processor between MySQL and FM databases. It didn't take long to run  
into performance problems on the FM side, so I've ended up doing a  
fair amount of testing: between FM and MySQL, and between the various  
methods of getting data in and out of FM.

I won't even address FM6; consider it a dead horse. For FM 7/8, here  
are a few things I've learned (which may be less applicable with FM9,  
but probably still persists to some degree):

1. MySQL is so much faster than FM as a back-end database, you can  
have horribly inefficient code talking to MySQL and it will still be  
faster than the most optimized code talking to FM. I'm talking so  
much faster that's it's hard to measure, like: FM takes 3.5 seconds  
to do operation X; MySQL takes... oh it's done by the time I get my  
finger off the start button on my stopwatch. So I use the microtime  
functions in PHP and discover that FM takes 3.5 seconds, and MySQL  
takes 3.5 milliseconds for the same thing.

2. There is a huge time overhead in any single XML request to FM.  
It's a lot worse on Mac-based servers than Windows-based servers,  
BTW. I consistently get the best performance by doing the least  
number of queries to FM as possible. That means it's faster to get  
data out of a single FM call to a single FM layout with portals to  
retrieve related data. than it is to make 2 separate calls to FM. The  
more data in the related record set, the worse the difference gets.

3. FM's API only allows updating a single record per call. SQL allows  
the updating of any group of records in one call. Because of the time  
overhead associated with any XML request to FM, an FM solution  
becomes rubbish if you need to update more than just a few records.  
Using portals to get the updates into a single call should help this,  
but I haven't tested that particular method and don't plan to.

This brings us back to the original issue of FM scripts vs. PHP: at  
some point (say, 20 records need changed) using FM scripts ends up  
being the best choice among poor choices to do multi-record processing.

Again, if your operations are primarily workgroup FM-based, with only  
a small, low-traffic web presence for specific stuff, whatever you do  
with FM is probably going to be fine. Processing power is cheap, and  
even when FM is dog-slow compared to MySQL, if the traffic is low  
enough, it won't make a practical difference to your users. This is  
where I'm at, and where I would suspect a lot of people on an FM list  
are at.

If your operations are mostly web-based, or you have a lot of web  
traffic (say, a busy online store or intranet for a big workforce), I  
honestly don't think it will matter that much how you approach  
getting data in and out of FM. The performance will be poor unless  
you throw a lot of hardware at it. A single server running Linux,  
Apache, MySQL, and PHP (LAMP), will be able to outperform a whole  
rack full of systems dividing up the tasks of running FM Server, the  
webconnector, and multiple IIS servers to respond to client requests.

Of course, the biggest cost of just about anything is the development  
time. Do you already have a good FM solution running? If so, you can  
probably throw a lot of hardware and licensing costs at expansion,  
and spend less money overall.

Oh well, I've got to get back to my real job now.  ;-)


On Oct 26, 2007, at 2:36 AM, Leo R. Lundgren wrote:

> Am I misunderstanding the features of portals? In other databases  
> you have the ability to JOIN tables, which seems much more flexible  
> to me in that with FM portals you can only go one level down a  
> hierarchy, so to speak. That's a pain in the ass, and it's made me  
> collect my data in other ways than using portals.


Derrick Fogle
derrick at fogles.net