8 Replies Latest reply on Oct 12, 2012 4:47 AM by TimDietrich

    API and the result set

    dloughlin

      In the application I've been buiding(Using FMServer 12 advanced, and the API, I allow users to create excel spreadsheets (using phpexcel). They can select fields they want to perform the find on, and enter a find value. They can also select the fields they want to use as the columns on the spreadsheet. This all works fine, until the result object gets too big, and it fails(usually a blank screen). Because of the number of columns they can choose from, the layout used has a lot of fields, which I'm sure is the biggest contributor to the problem. Other than reducing the number of fields on the layout (which I will probably do anyway), I'm trying to better understand the problem. Is there any setting in php.ini or in the API, or in the IIS, that controls the maximum size of a result object? Unless I'm missing something, I see no way to dynamically control which fields are on the layout? Does the field types have a significant impact on the size of the result object(ie; a text field presumably takes more space that a number). If a text field is empty I assume it takes up less or no space in th result object?

      A lot of questions I know, but just trying to better understand my problem.

      Any light shed would be appreciated.

       

      Dennis Loughlin

        • 1. Re: API and the result set
          TimDietrich

          Dennis --

           

          There are two ways to reduce the size of the result returned by the API: Reduce the number of records being returned (and there are a variety of ways to do that), and/or reduce the number of fields being returned. In your case, it doesn't sound like you can easily reduce the number of fields being returned, because the fields that a user might be interested in varies...

           

          But you could try "paging" the records being returned. In other words, instead of returning the entire set of records that meet the user's find criteria, return them in smaller batches. For example, maybe return 25 at a time. And you can do this using the setRange method. Here's a snippet of PHP that serves as an example:

           

          // Specify the range of records to return.

          if ( ! isset( $_GET['p'] ) ) { $_GET['p'] = 1; }

          $skip = ( $_GET['p'] - 1 ) * MAX_SITES_PER_PAGE;

          $max = MAX_SITES_PER_PAGE;

          $fm_request -> setRange( $skip, $max );

           

          If paging the results isn't an option, there is another technique that I've been using. It involves calling a script via the API, which in turn uses the ExecuteSQL function to "gather up" the records / fields that I need, and it returns them as one big delimited value (via a global field). It's tricky, but it significantly reduces the size of the result set - and it's very fast, too. This takes some effort, but it is another approach to consider. If you need details, let me know.

           

          Hope this helps!

           

          -- Tim

          • 2. Re: API and the result set
            mbraendle

            Dennis,

             

            it may be a memory limit, which is specified in the php.ini file for FMS(A) to be 128M.

             

            You may increase it by either editing the appropriate php.ini  (search for memory_limit), or using the ini_set() command in the the php script.

             

            May you try out and report back? Maybe I have to do that as well for some solutions.

             

            Martin

            • 3. Re: API and the result set
              dloughlin

              I had already plyed with ini_set the memory_limit, and it did not have any effect.

              I had taken a brief look at setRange, and I'll explore it further. As usual, there is not a lot of documentation I can find on it. Does it rerun the find with each iteration, or if used with newPerformScript, does it rerun the script each time?

               

              Thanks to both of you for your replies.

              • 4. Re: API and the result set
                TimDietrich

                Dennis --

                 

                You would use SetRange to perform one find at a time, and getting different "ranges" (subsets) of the found set. I don't know if that will work for you, because it sounds like you need the entire set of found records in order to generate Excel spreadsheets. But it is something to look into.

                 

                The second approach that I described - which involves building up only the fields that you really need, and for the entire found set, into a single field - might be best for your situation. It would take time to develop, but once it was done, you should see much more reliable find results, and see them more quickly as well.

                 

                -- Tim

                • 5. Re: API and the result set
                  dloughlin

                  If I understand what you said, using setRange, you would be rerunning the find each time, but the 1st time returns records 1-100, the second find would return     100-199, etc.?

                  Sounds like the second approach involves not using the api's find request, but rather using a script to do the find, and then combining the fields in one field with a deliminator of some kind, before returning the found set, which would just have the one field. Yes, a lot of programming, but in the end it should be the fastest. I may give that a try.

                  Again, thanks

                  • 6. Re: API and the result set
                    TimDietrich

                    If I understand what you said, using setRange, you would be rerunning the find each time, but the 1st time returns records 1-100, the second find would return     100-199, etc.?

                     

                     

                    Yes, that's correct. Again - probably not the best approach for your situation.

                     

                     

                    Sounds like the second approach involves not using the api's find request, but rather using a script to do the find, and then combining the fields in one field with a deliminator of some kind, before returning the found set, which would just have the one field. Yes, a lot of programming, but in the end it should be the fastest. I may give that a try.

                     

                    Actually, you could use the API to do a find request (probably the same code you've already written), and then follow up the find with a call to a script (using the setScript() method) which would "package" the result into a single global field. You would want to have that field reside all by itself on a layout that PHP can see. And then change to that layout after the find is done (using the setResultLayout() method).

                     

                    So maybe it won't be a lot of programming after all - just a few changes to your current PHP code, a new script to package up the result of the find, a new global field and layout, and some additional PHP to deal with the delimited result, and you're all set.

                     

                    Anyway, let us know how it works out... And good luck!

                     

                    -- Tim

                    • 7. Re: API and the result set
                      dloughlin

                      An update, I ahve been working on this off and on since last post, and I have it working well, or so it seems anyway, lol. The only remaining problem I come across, is fastcgi timeouts. They seem to occur when a very complex find is requested, that may take a few minutes for the DB to perform. I have been playing with some of the fascgi settings, and it seems to help, but have not been able to find any good references on the web regarding those settings.

                      At any rate, thanks for the idea about packaging the result into one global field.

                      • 8. Re: API and the result set
                        TimDietrich

                        I'm glad that you found the "global field" idea helpful. It sounds like you've made a lot of progress!

                         

                        Are you using the "set_time_limit" function ( http://php.net/manual/en/function.set-time-limit.php ) to prevent timeouts? The page that I referenced has some info there regarding its use and FastCGI.

                         

                        -- Tim