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

Wed Apr 5 08:29:33 MDT 2006

Another tip, 

We do logging for every page hit. I used to do a separate insert query, now
we add the log hit to a portal with the .0 portal feature.  Nice.

(Of course with a speedy odbc insert working now, I could log with that
without noticable performance hits)

Matt

On 4/5/06 8:18 AM, "Andy Gaunt" <andy at fmpug.com> wrote:

> 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