14 Replies Latest reply on Apr 12, 2011 1:37 PM by TSBear

    PHP API : Longer to load a large field instead of 10 smaller ones...

    Tommy_G

      Summary

      PHP API : Longer to load a large field instead of 10 smaller ones...

      Description of the issue

      I'm currently working on a web module for my company's new application and I need to get data from the database. On my layout, I have a field that has a lot of text(the equivalent of a 8-9 pages PDF). This field is in a portal that has 4 different block of information (basically all I need in 4 different languages). Now, as mostly everyone knows, the PHP API, when connected to a layout, will load EVERYTHING on the layout, regardless of if you use it or not on your PHP page. Since my PHP page shows 25 records at a time, that means it makes 25 queries to the database, each time loading those 4 big fields. I don't actually show those fields for the user, but they're necessary for search purposes. Anyway, when that field was added to the layout, my loading time when from instantaneous to taking between 20 and 25 seconds. After much testing, we found out that having 10 fields that each get a part of the information from the big field will give us a loading time of 1 to 2 seconds, making it WAAAAAAAAY faster. Is it normal for the API to struggle to load regular text from a field? In the end I'm actually loading the same amount of information, but the API as way more trouble loading 100 big fields(25 requests * 1 field * 4 languages) than loading 1000 small fields(25 requests * 10 fields * 4 languages) Tommy Gagnon

        • 1. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
          MartinBrändle
             Is there a difference in performance if you create another layout with the related table as main table and perform a separate query using this layout? What happens when you add the large field there?
          • 2. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
            MartinBrändle
              

            I did the test myself, using a database with 499 records. Each record has a text field that has 100 characters more than in the previous record. The first record has 100 characters, the last 49900. Then I did a series of XML requests for each of these records. Timings and XML result set sizes are reported in  /Library/FileMaker Server/Logs/web_server_module_log.txt and /Library/FileMaker Server/Logs/wpc_access_log.txt . On first inspection of these, it looks like the response time goes nearly linearly with record size, so in your case it could be a non-linear scaling effect when using portals, but in my case quasi-linear scaling when using a flat table.

            Exact results and the test files will be reported later this week. 

             

            BTW: In FMS 9 or later, you can reduce the number of portal rows returned in the XML resultset by using the -relatedset.max (limit portal records) query parameter. Cf. page 100 of the FileMaker Server Custom Web Publishing with XML and XSLT Guide. Would that be a solution in your case?

             

            • 3. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
              MartinBrändle
                

              Ok, here again with the test results.

               

              First, I have to correct my statement before. The dependence between cpu time to retrieve the XML result set and  field length is not linear, as I first guessed from quick inspection, but quadratic! See graph below. This explains everything what Tommy Gagnon observed.

               

              This is very bad news for the performance of the Custom Web Publishing Engine.

               

              Somewhere there must be a nested double loop in the CWPE  which should be eliminated to improve performance.

               

              CPU time vs. XML result set size 

               

               The spikes in the graph are caused when there were other XML accesses at the same time.

               

              The complete test set can be downloaded here 

              • 4. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
                TSGal

                All:

                 

                This question was originally asked in another post.  The reply is:

                 

                http://fm.lithium.com/fm/board/message?board.id=FM-en-4&message.id=22228

                 

                "If you are moving around a large amount of text, this has to be interpreted and pushed out to the browser.  This is an issue regardless of the API, and can definitely cause a "bottle neck" with PHP.  It is much easier to process lots of small fields rather than one large field.  Since you mention "8-9 pages PDF", that is big.  Therefore, you're doing the right thing by breaking up the field into smaller amounts of text."

                 

                Martin Brändle:

                 

                Thank you for taking the time to test this out and provide results.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
                  MartinBrändle
                    

                  TSGal,

                   

                  the test was made by me so that NOTHING is pushed to the browser, so we can exclude that. We can also exclude that the response time depends on a search of the text field because I search the record ID and then just fetch the text field. It's no matter of the string structure in the field because I used random words to fill it. So it must be a pure property of the CWPE or the connection between the CWP and the database server.

                   

                  Responsible could be any of those parts:

                  - the database server side which produces the XML result set

                  - the protocol between the database server and the CWP core for transmitting the XML result

                  - the Xerces XML parser of the CWPE

                  - Xalan that needs to assign the XML stream to a variable in my test

                   

                  It should be common sense that if you double the size of a string that the time to process it should double as well (+some offset). However, it more than doubles. Something IS wrong with CWP, and this since FMSA 7.

                  • 6. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
                    Tommy_G
                      

                    I had posted this at 2 different places, sorry about this.

                     

                    Anyway, you get what I meant with your test. Even if you don't show anything from your search, the search still takes forever to be done, meaning the problem isn't with PHP itself.

                     

                    Thank you for taking the time to run this test. I'm mostly a web programmer and designer, so I create designs for our softwares and do the web modules. Since the programming team is kinda busy right now, I really wouldn't have been able to ask for help. It took a few days to go the simple database I sent TSGal, which contains 25 records with one field that has 8 page worth of text on it.

                     

                    And no, removing the amount of portal rows loaded wouldn't have helped me, as I need to search in every single one of them. :P

                    • 7. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
                      MartinBrändle
                        

                      You can still put the portal on the layout for searching. The  -relatedset.max just parameter has as only effect that the portal records won't be included in the XML resultset. So you should see a performance increase.

                       


                      Tommy_G wrote:

                       

                      And no, removing the amount of portal rows loaded wouldn't have helped me, as I need to search in every single one of them. :P


                       

                       


                      • 8. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
                        Tommy_G
                           Right, but I also need to show that information. If I can search in the portals but the data isn't brought back to me, there's no use.
                        • 9. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
                          TSGal

                          Tommy_G:

                           

                          Just to keep you updated, Development and Testing have confirmed the slowness problem (as expected).  No other information is available at this time.

                           

                          TSGal

                          FileMaker, Inc. 

                          • 10. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
                            hschlossberg
                               I could be mistaken in my understanding here, but perhaps the problem has nothing to do with PHP.  In FM10, putting that much text into a text field will bring your database to a halt as soon as you make that field active.  I was recently shown a file that had about 16000 lines of text in it (short lines; just record IDs).  The system freezes for about a minute each time I click into that field.
                            • 11. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
                              MartinBrändle
                                

                              It has nothing to do with PHP. It's already the XML production which is slow (see my previous post).

                              Regarding field length in FM Pro: I tested my file by just clicking in the text field of the largest record (49900 characters). No stalling there, whether the file is local or hosted. 

                              In your case: are the record IDs a multikey used in a relation?

                              • 12. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
                                hschlossberg
                                  

                                Martin Brändle wrote:

                                It has nothing to do with PHP. It's already the XML production which is slow (see my previous post).

                                Regarding field length in FM Pro: I tested my file by just clicking in the text field of the largest record (49900 characters). No stalling there, whether the file is local or hosted. 

                                In your case: are the record IDs a multikey used in a relation?


                                I'm honestly not sure what the practical purpose was for this data, as it is a sample file provided to me by another developer.  My purpose, therefore, is not to find a workaround, but rather to test and report the issue. 
                                The value in the field has a valuecount() of 16,323 and a length() of 113,377.  I'd be happy to provide you a sample of the small test file if you want to send me a private message that includes your email address.

                                 


                                • 13. Re: PHP API : Longer to load a large field instead of 10 smaller ones...
                                  Tommy_G

                                  It has been over a year now and I'm wondering if there's any update on the subject? The project I was working on stalled for months while we worked on something else and we ended up starting this one over. Now we've reached this point again. Haven't personally had the time to try it since I'm busy on other stuff but I'm just wondering if it was eventually fixed? I know TSBear is subscrined to this thread. Maybe he knows?

                                  • 14. Re: PHP API : Longer to load a large field instead of 10 smaller ones...

                                    Tommy_G:

                                    We have not released an update that addresses this issue. I'll post to this thread with new information as it becomes available to me.

                                    TSBear

                                    FileMaker, Inc.