14 Replies Latest reply on Oct 18, 2016 11:00 AM by RussW

    Find - Optimaztion and understanding the inner workings of FMP

    RussW

      One of the authors of an FMP paper on Optimization said that learning FMP was a journey. Boy, was he right.

       

      So, after reading a host of discussions and papers on optimization, I have found some seemingly contradictory statements. My chief concern at the moment is the FIND when used on a large table of many fields and many records. And there seems to be some confusion, probably only in my own head. Hence my reach-out here.

       

      In one place it was described that a FIND on a complex table should be performed on server using PSoS. Easy enough but I have still not mastered the ability to return the Found Set to the client. As I researched how to do this, I read that I'd get the same efficiency by doing my find in a blank layout based on the table I am searching. The articles say that by searching on a blank table, no data is transferred over the WAN except for the found set of record IDs which is negligible data. If so, that seems like a lot less code than replicating a found set on the client, but I'm not sure I can use blank layouts effectively in iOS which doesn't support off-screen windows.

       

      What do you pros that live and breathe this stuff recommend?

       

      Thanks,

      Russ

        • 1. Re: Find - Optimaztion and understanding the inner workings of FMP
          Johan Hedman

          To save a Found Set you need to work with Snap shot links. You can save those inside a FileMaker Script or from the FileMaker Menu. Then you save this file in a Container field and open it up on another place. The perform large Finds will always be much faster in Server, so using Perform Script on Server will be suitable if you have a lot of data and many script parameter. But FileMaker are still a very powerful tool, so to reach that level where you need to so advanced search that you need to do a PSoS it is not on a novice level anymore. So stay with ordinary FileMaker search and do not jump into PSoS before you actually see that you need it

          • 2. Re: Find - Optimaztion and understanding the inner workings of FMP
            philmodjunk

            You can also use exit script to return a list of primary keys back to the script that called the PSOS and this can be used to produce the desired found set.

             

            I read that I'd get the same efficiency by doing my find in a blank layout based on the table I am searching. The articles say that by searching on a blank table, no data is transferred over the WAN except for the found set of record IDs which is negligible data.

            From what I have heard directly from many sources, including FileMaker Engineers, this is not the case. And a blank layout is not the same as a blank table. When a client "fetches" 1 or more records, you get the whole record--every field in that record. This is where the suggestion usually surfaces that you need to try to keep your tables "narrow"--only have as many fields in the record as the user normally needs to see on the layout. You can use a one to one relationship to link to a second table where additional, less often needed fields are included, but not needed for your search, for example.

            • 3. Re: Find - Optimaztion and understanding the inner workings of FMP
              RussW

              First off, thanks for the quick responses. As it is, I am in this predicament because my finds are killing me so I do have a need. I have a fairly good grasp on DB design, albeit some of it ancient, so narrow tables and removing data redundancy, etc., are pretty well in hand. It's the whole context thing that keeps me guessing and how to best utilize the server without bogging that down as well.

               

              That brings me to PSoS finds and retrieving the Found Set. I understand Snapshot Links or returning a ListOf value by Exit Script result. The few articles I have found then tell me to do a loop of finds on the client to get the records. Doesn't that put me right back where I don't want to be? Won;t the find a specific record or consecutive set of records be painfully slow over a slow WAN connection?

              • 4. Re: Find - Optimaztion and understanding the inner workings of FMP
                Johan Hedman

                Loops will not be fast. ListOf could be fast, but I think if you have large data sets and complicated searches a PSoS that save a Snapshot Link on FMS and then stores in a container field that you then opens up on FMP and execute will most probably be a lot faster the Loop

                • 5. Re: Find - Optimaztion and understanding the inner workings of FMP
                  RussW

                  Basically I would just be searching for an indexed key returned in the list. Nothing complicated. But I'm worried that doing a find of the records in the list, the list being the found set of records, would be just as slow as doing the find on the client in the first place.

                  • 6. Re: Find - Optimaztion and understanding the inner workings of FMP
                    Johan Hedman

                    As long as you not are search on Not calculated field you are pretty safe with just doing ordinary searches. There is of course faster to do a  search on a field that the table of your layout is based and rather the related fields, but still FileMaker are fast.

                     

                    How would a complicated search be for you?

                    • 7. Re: Find - Optimaztion and understanding the inner workings of FMP
                      RussW

                      Searches aren't complicated. I generally am searching for transactions for a date or a given range of consecutive dates. Dates are indexed. BUT, I have calculation fields that draw from related records, so I cannot "save" the calculated result. This is what bogs down the search. On the server, the finds are instant. On the client, they can take a minute on a small test sample.

                      • 8. Re: Find - Optimaztion and understanding the inner workings of FMP
                        Johan Hedman

                        You can have a numeric field where you store you calculated data in that you have FMS to do a Scheduled Script to save the data in. Then you would have a numeric and indexed field to search that would be very fast.

                         

                        I often use this technique for places where I know my users will do a lot of searches on. Often this field are calculated once and if I need to update them later I can just have a ScriptTrigger to do another change to that value.

                        • 9. Re: Find - Optimaztion and understanding the inner workings of FMP
                          RussW

                          Thanks, Johan. Helpful information but I don't think it really addresses my core question of optimization and understanding what's going on under the hood of FMP.

                           

                          While chatting, I created a 230,000 record table with a complex unsaved calculation and another field to save the calculation at the instant I press a button. I did a PSoS FIND on a subset of 75 records stored in a working table (utility table?) that just stores the record primary keys. Then I used a GTRR on the client and it was darn near instant.

                           

                          This didn't seem to work for me last night, but then I was really tired of all of this FMP stuff. Had to leave and watch the Indians win over Toronto. And I'm not even a sports enthusiast!

                          • 10. Re: Find - Optimaztion and understanding the inner workings of FMP
                            RussW

                            Back to the group at large, if I use the Snapshot link, are you saying I can "execute" the link from within FMP or, as I have read, do I have to parse the file for the list of records in the found set and do a loop of finds on the client?

                             

                            And, if I have to do the loop of finds on the client, how is this any different that just searching the whole file on the client in the first place?

                            • 11. Re: Find - Optimaztion and understanding the inner workings of FMP
                              philmodjunk

                              With a returned list of primary keys, you can set a global field to that list and then use a relationship with Go to related records to reproduce the found set.

                               

                              One trick, that has to be tested carefully, but which can produce substantial improvements in find performance is to do what I call a two stage find.

                               

                              in stage one you perform a find specifying only criteria in stored/indexed fields.

                              in stage two, you return to find mode and specify the criteria in unstored/unindexed fields and constrain the found set

                               

                              If state one can consistently produce a reasonable sized found set, stage two can then winnow the found set down much faster than finding with criteria specified in un-indexed fields in a single find.

                              • 13. Re: Find - Optimaztion and understanding the inner workings of FMP
                                wimdecorte

                                RussW wrote:



                                In one place it was described that a FIND on a complex table should be performed on server using PSoS.

                                 

                                Can you provide a link to this source?

                                I think it is bogus and potentially very dangerous to reduce complex matters into "must" and "should" recommendations like the one you stumbled on.

                                • 14. Re: Find - Optimaztion and understanding the inner workings of FMP
                                  RussW

                                  I'll see if I can find it. I read so many papers and articles. Hopefully my web history will allow me to find it.

                                   

                                  I'm also not certain the author said "should" or "must" but rather "this is how". Most of the article, if I am remembering the right one, spoke about parsing the Snapshot Link file. AFter which he said you can use the fund set of records to loop and find the ones you need.