8 Replies Latest reply on Mar 26, 2014 9:20 PM by Vyke

    Inconsistent PHP speed

    submarine

      I use PHP API to retrieve some data from a ODBC table in FM. A dedicated layout is created for this purpose with 8 fields, all are text or numeric fields and will be displayed on the PHP page.

      The table has 4 records currently and the search will return just one record.

       

      The PHP is very simple with a db connect > Find with 1 field without sort > getField > display fields.

      When I run the PHP script the speed varies.

      When it is first run, it takes approximately 10 sec.

      I run a microtimer in PHP to check which part of the script causes the delay. It seems that the Find part is causing the delay.

      Subsequently, when I re-run the script repeatedly, it generally takes less than 1/10 sec to process until the 30th+ time (see the results below). After the 30th+ delay, it's back to less than 1/10 sec until another delay at 60th+ time. This pattern keeps repeating.

      At first, I thought it is the ODBC issue. I created another local table with the exact table structure and records. The result remains the same.

       

      Now I suspect it is some kind of database session that PHP maintains with FM and it will expire after some short interval. The reconnection causes the delay.

      Can anyone of you confirm this? If so, how can I extend the interval?

       

       

      First Test

      Start: 1395387977.3365
      Aft dbcon: 1395387977.3551
      Aft Find: 1395387987.0884 (around 10 sec)
      Aft Diplay Data: 1395387987.0886

       

      2nd Test

      Start: 1395388000.6743
      Aft dbcon: 1395388000.6881
      Aft Find: 1395388000.741 (less than 0.1 sec)
      Aft Diplay Data: 1395388000.7413

       

      3rd Test

      Start: 1395388011.9376
      Aft dbcon: 1395388011.9514
      Aft Find: 1395388011.9982 (less than 0.1 sec)
      Aft Diplay Data: 1395388011.9985

      ....

       

      30th+ Test

      Start: 1395388045.7118
      Aft dbcon: 1395388045.7254
      Aft Find: 1395388055.0348 (around 10 sec)
      Aft Diplay Data: 1395388055.035

       

       

      Many thanks.

        • 1. Re: Inconsistent PHP speed
          beverly

          1. are you using FM as an ODBC source? I suspect NOT, as you said API for PHP which implies the XML is pushed/pulled and SQL queries are not made to FM

           

          or

           

          2. are you using FM, but linking to a SQL server ODBC source?

           

          If the latter, you know that you can access the SQL *directly* with PHP, right?

          To optimize, use the dbs as they were intended. You can "mash" results from serveral dbs (SQL and FMP, for example) to your display.

           

          Beverly

          1 of 1 people found this helpful
          • 2. Re: Inconsistent PHP speed
            Vyke

            Beverly is correct but I would also like to add that, FileMaker server has some issues with releasing clients accessing via php (pre 13, haven't done indepth testing with 13 yet). When I notice that the server isnt releasing the connections and they start stacking up, the query time increases drastically.

             

            I would suggest as Beverly does, that you access the SQL DB using SQL queries in your PHP and then combining what you need to in your script with data directly from FileMaker tables.

            1 of 1 people found this helpful
            • 3. Re: Inconsistent PHP speed
              submarine

              HI Beverly and Aryden,

               

              Thanks for the advices.

              Yes, I know that I can quiry data directly from SQL and speed may not be an issue anymore.

               

              But this may not be the solution as the same issue applies to a local table which was replicated from the ODBC table.

              This is my first project on PHP and I am concerned that the speed issue will affect all FM tables whether or not they are from ODBC or are native FM table.

              • 4. Re: Inconsistent PHP speed
                taylorsharpe

                I am not a PHP developer, but the ones I talk to have complained to me about the FileMaker PHP API in that only allows a single call between close and opening a connection (you can't batch several calls, etc.).  It works fine in FileMaker, but is often slower compared to say making connections with a MySQL database.  Maybe this is something FileMaker can improve upon in the future.  For this reason, when working with web developers, I often store the data primarily used by the web service out on a MySQL database and just make an ESS connection to it for FileMaker.  I used to do this on the same machine as FileMaker, but now FileMaker Tech support is saying not to have any other services on the same machine.  FileMaker can be your web services database, but it is not what it is best at.  On the other side of things, MySQL is the most hacked database in the world and if you data is sensitive, keeping it on FileMaker and following the FileMaker security guidelines is much safer. 

                • 5. Re: Inconsistent PHP speed
                  Vyke

                  Yes, tech support says dont run anything else, however, I run Server 11 + IIS7 + SQL Express on one machine, Server 13 + IIS7 + SQL Express on another and have absolutely no issues with them at all. The only time an issue occurs is when I write my php to do multiple calls to FMSRV and they dont get released. The solution is to rewrite my classes and methods so that I only make 1 connection at a time.

                  • 6. Re: Inconsistent PHP speed
                    submarine

                    The same issue was reported by another user - https://fmdev.filemaker.com/message/85006#85006

                    I shall write my future posts there.

                    • 7. Re: Inconsistent PHP speed
                      submarine

                      Just to close this post and give a clue to those who may encounter the same issue -- it is the authentication process with external AD groups that causes the lag.

                      Moving the AD groups to the bottom of the authentication list in FM works for me.

                      • 8. Re: Inconsistent PHP speed
                        Vyke

                        Tried it a while back, didnt have success with it. Still slow.