[FX.php List] FX.php and SQL queries against FMSA 8

Wed Apr 5 07:18:08 MDT 2006

And if I may Matt,

For all those doing multiple queries to find related records... start using
portals. The data for portals inside the FX array is just a simple 'for
loop' away from being used and this is much quicker than multiple queries.

We have pages that perform a single query using over 10 portals on the
layout to return information AND build the HTML page (including load
graphics) in under 2 seconds (oh, and we write a new record to FileMaker in
that time and pull ads from an external PHP Ad server).

Andy Gaunt
T: 407.810.4722
andy at fmpug.com
http://www.fmpug.com    

Recipient of FileMaker's 2005 "Mad Dog" Public Relations Award

For chapter locations, dates & times please visit the website at
http://www.fmpug.com If you can make it to a meeting, please RSVP at
http://www.fmpug.com/rsvp.php

-----Original Message-----
From: fx.php_list-bounces at mail.iviking.org
[mailto:fx.php_list-bounces at mail.iviking.org] On Behalf Of Matthew White
Sent: Wednesday, April 05, 2006 9:53 AM
To: FX.php Discussion List
Subject: Re: [FX.php List] FX.php and SQL queries against FMSA 8

Hey all,   

We have a relatively complex php based system and are getting bit by the
performance bug as well. We are using the xml in fx.php and individual
queries are fine performance-wise (return results in 1-3 seconds for up to
hundreds of thousands of records), but we get hammered on simultaneous
queries.  The web connector just bogs down on multiple queries
simultaneously.  It also hogs massive cpu resources on the server.

Also, any query against the xml interface takes an absolute min of .5
seconds for us.  We have tested extensively and feel confident that is true.
This means if a process makes a series of queries, the time is chained, i.e.
A process that requires 5 queries would take an absolute min of 2.5 seconds,
but that is ideal, in fact it can take 5-6 seconds easily. Tie that together
with mutliple simultaneous queries from different clients and performance
goes down the drain.

What we have discovered is that the native odbc calls without fx reduce min
call time to less than .1 seconds.  That is a lot better.  That was a simple
insert to a log file, but still much better. Now we are evaluating switching
our fx xml calls to ODBC, but I am getting sql parse errors back from the
ODBC driver when using fx.  I am trying to troubleshoot that now.

(If Chris is listening, I would love to relate my problems about the fx odbc
errors I am getting :)

Overall, it looks like the response from the data base engine is excellent,
but the web connector is hamstringing us.

BTW, make sure you don't have un-stored calcs on the layouts you are using
for the xml queries.  They will kill you.  Also untapped errors running
scripts totally hose the web connector. You may get totally instability in
the xml interface if you get a scripting error in filemaker. Moral of the
story, avoid running scripts or trap errors very carefully.

Just my 3 cents worth.

Matt

On 4/4/06 11:08 PM, "Jason H Awbrey" <jawbrey at harmonic-data.com> wrote:

> John, Joel and Chris,
> 
> Thanks for the answers. Just so I don't sound like a total nut case,
> let me elaborate a bit. I have a solution that is tied to an ENORMOUS
> FileMaker system. Due to the complexity of the system, the CWP
> interface via XML/FX.php is slower than (I believe) it should be.
> Query times at this point are in the 7-12 second range. That's about
> 6-11 seconds waiting for FileMaker to respond (work through the
> metadata) and then ~1 second to return the XML. There have been some
> version updates that could give us a performance boost, but I won't
> get to implement those until I move everything to a new server tomorrow.
> 
> Now that Chris has added the ODBC features to the FX.php class, I was
> thinking it would be easy to test and see if the ODBC interface would
> be any faster than the XML interface by simply changing my queries to
> use the ODBC query string. It is understood that under normal
> circumstances the ODBC interface would be a bit slower than the XML
> interface due to the information that it has to return. The thought
> would be to create an ODBC interface file with an abstracted TOG of
> just the TOs we would need access to.
> 
> If that doesn't get  the desired results, I've got a trick or two
> more up my sleeve using XML, they're just a bit more involved. I'll
> bark up that tree when I get there...
> 
> Isn't optimization fun?!?
> 
> Cheers,
> Jason
> 
> 
> ~-~-~-~~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
> Jason Awbrey
> Web Integration
> FileMaker 7 Certified Developer
> Harmonic Data Associates
> http://www.harmonic-data.com
> jawbrey at harmonic-data.com
> w - 214 269.2804
> ~-~-~-~~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
> 
> 
> On Apr 4, 2006, at 7:28 PM, Chris Hansen wrote:
> 
>> Jason,
>> 
>> Of course I have; I built the thing =)  Granted, I should echo what
>> others have said about performance, but you can do some cool things
>> (e.g. updating every record that matches specified criteria, etc.)
>> And, I didn't find performance to be THAT much slower than
>> accessing FileMaker via the XML interface.  In the case of ODBC,
>> your DSN is entered using SetDBData(<DSN>).  Seemed the most
>> logical use of FX syntax for an ODBC connection.
>> 
>> Just FYI, setting everything up on Mac OS X (as opposed to Windows)
>> can be hairy (or was when I did it) because of differences in the
>> way that the ODBC drivers are set up in the OS versus PHP.  (That
>> may have changed by now, though.)
>> 
>> Best,
>> 
>> --Chris Hansen
>>   FileMaker 7 Certified Developer
>>   Creator of FX.php
>>   "The best way from FileMaker to the Web."
>>   www.iViking.org
>> 
>> 
>> On Apr 4, 2006, at 3:52 PM, Jason H Awbrey wrote:
>> 
>>> Hello all,
>>> 
>>> Has anyone used FX.php to query FileMaker via the ODBC interface?
>>> It seems as though I would need to specify a DSN somewhere but
>>> don't see anywhere I would be able to do that. Does anyone have a
>>> simple example they could share that could get me pointed in the
>>> right direction?
>>> 
>>> Cheers,
>>> Jason
>>> _______________________________________________
>>> FX.php_List mailing list
>>> FX.php_List at mail.iviking.org
>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>> 
>> 
>> _______________________________________________
>> FX.php_List mailing list
>> FX.php_List at mail.iviking.org
>> http://www.iviking.org/mailman/listinfo/fx.php_list
> 
> 
> 
> 
> 
> 
> _______________________________________________
> FX.php_List mailing list
> FX.php_List at mail.iviking.org
> http://www.iviking.org/mailman/listinfo/fx.php_list

-- 
Matt White
Director Of Development
SciMed Solutions
(919) 287-1817
(919) 540-3283 (Voice Mail, Pager)
matt at scimedsolutions.com

_______________________________________________
FX.php_List mailing list
FX.php_List at mail.iviking.org
http://www.iviking.org/mailman/listinfo/fx.php_list