5 Replies Latest reply on Aug 3, 2012 6:50 AM by Oliver_Reid

    SQL 2008 slow Filemaker / PHP export observed

    fmmotorola

      We are trying to provide a web based app (Internal Facing) to users to search a large set of product data and export based on their filter criteria.

       

      We have built the layout in Filemaker, created a PHP site (via the Site Assistant), and loaded 2 million + rows into our SQL Table. We have created the necessary relationships and linked the external data sources properly in the FM app. We are able to search the data, we are able to export, but the export process either hangs or takes a very long time to complete even with small result sets of 2000 rows or less.

       

      Current Architecture

       

      • Filemaker 11 Server (Both FMS and PHP Server)
      • Filemaker 11 Clients or PHP Web
      • Tables and data are in SQL 2008 (Dedicated Server)

       

      What are some recommended best practices for improving performance of external datasources with FM / PHP front ends ??? Initial research indicates SQL views might address some performance, but will lose all FM layout flexiblity.

       

      Thanks for any and all input

       

      Glenn Cooper / Robert White

      Motorola Solutions, Inc

        • 1. Re: SQL 2008 slow Filemaker / PHP export observed
          Oliver_Reid

          I assume the FM server and SQL machines are on the same LAN?

           

          Not sure when you say "export" where the PHP is involved?

           

          Exports should be no problem from ESS tables when executed from FMP: but avoid export shadow calc fields as these have to be computed on the fly during the export.

           

          Rather than filter using FMP, maybe use FMP to set parameter values in SQL and the set up a "view" that does the filtering in SQL. The view can also contain an calculated values you need. ( I assume you know the SQL syntax to do all this?)

          That way SQL does all the heavy lifting in filtering the data and FMP is effectively doing a simple dump.

          • 2. Re: SQL 2008 slow Filemaker / PHP export observed
            crw030

            Oliver.

             

               Thanks for your response. 

             

               1. The FM Server and SQL Server are both in the data center on 1GB connections.  They are actually on the same subnet of the same LAN.

             

               2. we were planning to deliver a very basic search and export functionality using the PHP capability of FM  (Site Assistant built website).  This was primarily to avoid FM client installs at every potential user.   A side benefit is basic reporting, data editing etc can be done by the devs using FM - without the need to build this functionality into the web.

             

               3.  Basic performance of exporting ESS data from a base table and related table as part of a single export seems to incur a serious performance hit (appears to be performing a query against the related table on a PER_ROW basis).  Moving data from multiple tables into a view appears to address this issue - but requires extra SQL work (creating every view required by various layouts).

             

               4. Haven't tried this, but I presume you are thinking a working table where FM stores the search criteria and then that search criteria is automatically used to filter a standard view.  Presumably based on SID (or some identifier the systems can share)  to support multiple simultaneous users.

             

              Our problem was we were trying to show FM's capabilities as a go-between, but if we need todo significant work on SQL anyhow to make the solution work - FM adds very little to this specific project.  An added complication was the PHP implementations for exporting xls / xlsx are memory hogs (we are exporting tens-thousand rows at a time).

             

               Maybe as a final thought, is anyone really delivering web apps around FM?   We're trying to move away from IWP at the recommendation of our FM sales/engineering contacts.  We are looking at using the PHP access capabilities of FM - but it seems at every turn to have poor performance.  We're getting alot of pressure just to abandon FM for some projects and redo in ColdFusion over SQL Server because FM seems to be a big performance bottleneck in this use case.

             

               My concern was if maybe we had a configuration problem, or someone has seen some FM/PHP best practices for delivering realtively decent performance web portals over FM databases.

             

            Thanks Again.

            Robert

            • 3. Re: SQL 2008 slow Filemaker / PHP export observed
              Oliver_Reid

              I am not an export on the Excel export efficiency of PHP especially using the FM API

               

              I do web/FM projects by using ESS to store the data and using php + ODBC + SQL

               

              Have done a site where a SQL query is edited and actually stored (using FM) in an ESS SQL field. PHP loads that (using ODBC) and executes the query. Another SQL field contains parameters that PHP uses to install filter controls for the web page so that users can further filter the results. It works very well.

               

              I threw this up for non profit I support

               

              see http://edpsoccer.org

               

              and select the Alumni page from the left side menu as a good example.

               

              If you can find an  efficient PHP class to export to excel this should be fine. Contact me back channel if you want to discuss extending this concept.

              • 4. Re: SQL 2008 slow Filemaker / PHP export observed
                crw030

                Oliver.

                 

                   Thanks again - I took a look at the website you provided, and performance was quite good.

                 

                   For a project like that can I safely assume you are simply using FM to provide some basic front-end  (possibly administrative) capabilities?  It seems, based on your response, the FM Server isn't really involved in the day-to-day website  (maybe I misunderstood).

                 

                   I apprecaite your level of detail to your responses, maybe we're just trying to use FM in a non-typical way.  I can always provide administrative capabilities via FM client  (where I can absorb the license cost) and maybe provide basic user interface on the web with PHP or ColdFusion to the same backend  (via ESS) database.  That's where we are at this moment - FM is simply providing easy access for admin purposes  (browsing, doing what-if etc).

                 

                  I have an open ticket with Filemaker - I'll respond with whatever "fix" they recommend from there.  Maybe they have recommendations on how to setup ESS differently (or use it differently) to address the perceived performance impact.

                 

                  Robert

                • 5. Re: SQL 2008 slow Filemaker / PHP export observed
                  Oliver_Reid

                  I suspect the issue is the efficiency of the code generated by the site assistant, then the fact the that is using teh FM API to query FM , which is using an ODBC driver to query SQL and then coverting and sending the result back to the web user (It's delivered as XML, I believe)

                   

                  With the number of records you are dealing with it is likely be slow.

                   

                  PHP/SQL coders are more readily avaiable than CF, but if yuu have a CF shop in house they should be able to get an export to excel from a SQL query working for you quite well.

                   

                  I have SQL table called "tablequeries". Various fields specify the SQL query, the coulmn widths, which columns to display, which have filter controls, and the type and arrangement of the filter controls on the page. All that is edited using FM/ESS.

                   

                  PHP loads a tablequery record and sets up the page. All that would need in yuor case is a "send to excel" button that dumps the result to Excel. e.g.:

                   

                  http://www.dougboude.com/blog/1/2009/06/PHP-Export-to-Excel-Snippet.cfm