9 Replies Latest reply on Oct 23, 2012 10:37 AM by jbante

    Portal filtering transfers a HUGE amount of data?

    justinc

      Hey all,

      I am trying to create a dashboard type thing, with a list of upcoming events. So I have a portal showing the events that match a range of dates. But it seems that no matter how I slice this filtering business the layout will transfer about 18MiB of data down, which takes quite a while (20 - 60 seconds).

       

      I have tried the regular filtered portal, using a few different filters:

       

      1) Explicit list of dates in the range (yeah, not real dynamic, but was just a test):

      Case (

      Event::TheDate = Date (10;1;2012) ; 1 ;

      Event::TheDate = Date (10;2;2012) ; 1 ;

      ....

      Event::TheDate = Date (10;19;2012) ; 1 ;

      Event::TheDate = Date (10;20;2012) ; 1 ;

      0

      )

       

       

      2) More compact date range with inequalities:

       

      Event::Date ≥ Date (10; 1; 2012) and

      Event::Date ≤ Date (10; 20 ; 2012)

       

       

      3) A friend suggested using a Relationship Filter (with a different TO) that is defined by a global field, which in turn is populated with IDs by script. The script makes an ExecuteSQL call to get the IDs for records matching a range:

       

      ExecuteSQL( "SELECT id FROM Events WHERE TheDate BETWEEN ? and ? "

      ;"" ; "" ; "10/1/2012" ; "10/20/2012")

       

       

      But each of these moves around the same amount of data, even though some are much longer than others. I have moved the date ranges around to return anything from 0 records to 234 records, and each time it is the same amount of data. It really just seems like I am getting the entire table of data and the filtering is happening locally. (The table is rather wide - 700 fields, and has 3100+ records, average record size is 15KiB, which doesn't multiple out to 18.)

       

      What's going on here? I thought filtered portals were supposed to happen at the server in FM12? (FMSA, BTW. Clients are remote; FMPA12.) It is filtering SOMETHING (if the average record size is accurate), but it certainly doesn't appear to filtering down to the actual records visible, at least not until I get it.

       

       

      Thanks,

      J

       

      UPDATE (before I even posted ) : While the SQL-and-relation system was noticably faster than the portal filter (the 20 seconds part of the range), it appears that the 'Between' key word was very slow. I ran a version that just used "Where TheDate > date1 and TheDate < date2" and it ran in about 1 second. Nice! That is pretty bearable. But that still leaves the question of what FM is doing in the other circumstances.

       

      And can you use ExecuteSQL inside the portal filter definition box somehow? It seems that the statement I used was about as simple as it might get for that definition.

        • 1. Re: Portal filtering transfers a HUGE amount of data?
          gdurniak

          I don't see any reference to say portal filters were improved

           

          Portal Filters are a tease, because they do a "table scan", examining each found record, and so are inherently slow

           

          It only works well for a small set of data, perhaps already pre-filtered

           

          greg

           

          > What's going on here?  I thought filtered portals were supposed to happen at the server in FM12?

          1 of 1 people found this helpful
          • 2. Re: Portal filtering transfers a HUGE amount of data?
            BruceRobertson

            I am curious how you established how much data was transferred?

            • 3. Re: Portal filtering transfers a HUGE amount of data?
              jbante

              From what I can tell, FileMaker 12 does move some portal filtering to the server, but not everything. Calculations that reference any client-specific state seem especially likely to be handled client-side after all the relationship match records are transferred — for example, any filter that references a global field. In these cases, FileMaker has to transfer all the records that match the relationship criteria over the network, and the client has to run the filter calculation against every single record. As gdurniak stated, portal filters work best on small sets of records (dozens, not hundreds). Relationship-based filters can be much faster because they can take advantage of field indexes, and because the server more reliably controls the result set of records sent over the network. For large record sets, the method of setting a global field with a list of IDs from ExecuteSQL is the more performant way to go. Futher filtering on the portal makes the most sense when that result set is on the scale of dozens of records or less, and the filter criteria are "non-trivial," i.e., the kinds of things you can't encode in a relationship without adding special-purpose calculation fields to one or both sides.

              1 of 1 people found this helpful
              • 4. Re: Portal filtering transfers a HUGE amount of data?
                justinc

                I was using Wireshark to measure network traffic on Port 5003. 

                • 5. Re: Portal filtering transfers a HUGE amount of data?
                  justinc

                  Well, I may have been generalizing a bit, but they were statements that I heard at DevCon.  Here are my notes from various sessions with the pertinent statements (in reviewing them, they :

                   

                  Server & WAN performance

                  Jon Thatcher

                  ...

                  WAN Performance:

                      FM11 - all portal filter on client

                      FM12 - if possible filter calc added to JOIN

                          much smaller set of record IDs

                          Optimization is limited (currently); Get() functions & constants ARE optimized; global fields are NOT.

                  ...

                   

                  =================

                  FM Server config best practices

                  Mathew O'Dell

                   

                  ...

                  What FMS has done:

                  ….

                      Filtered portals (simple)

                   

                  =============

                   

                  LAN/WAN Performance - The Basics unplugged

                  Mark Richman & Greg Lane (Skeleton Key)

                  ...

                      - Portal filter now done on server!

                  • 6. Re: Portal filtering transfers a HUGE amount of data?
                    justinc

                    Jbante,

                        Thanks for the reply.  It has  good detail and explanation in it.

                     

                    In reference to your statement about local client state NOT being a good idea for portal filters, do you have any insights as to why it is that a global field for a relationship filter is better than a global field for a portal filter?  I have seen it work in this case, but it seems odd.

                     

                    If portal filters aren't very handy on larger record sets, that makes them rather moot.  How often is it that you have a table with only a few dozen records in it?

                     

                    On a related note, it appears the FM's SQL engine isn't that great at generating the final calculus, based on the performance difference seen with my using a range of inequalities vs. using keyword 'Between'.  That is very annoying, that you have to experiment with multiple versions of the same statement to figure out which one works better.

                     

                    --  J

                    • 7. Re: Portal filtering transfers a HUGE amount of data?
                      justinc

                      Here is an interesting statement that I found just now (from FM's website):

                       

                       

                      Portal filtering does not work with some operators in hosted files

                        Answer ID: 11120 Last Updated: Jul 23, 2012 02:56 PM PDT

                       

                      Products
                      • FileMaker Pro                       
                      •                                12.x                       
                      • FileMaker Pro Advanced                       
                      •                                12.x                       
                      • FileMaker Server                       
                      •                                12.x                       
                      • FileMaker Server Advanced                       
                      •                                12.x                       

                       

                          ISSUE:

                      Portal filter does not work with the following operators in hosted files:

                      • ≠ (not equal)
                      • ≤ (greater than or equal) and (less than or equal) ≥

                      The portal is properly filtered when the FileMaker Pro file is accessed locally.

                       

                      EXPLANATION:

                      This issue only occurs when a FileMaker Pro 12.0v2 client accesses the file when hosted via FileMaker Pro 12.0v1 or FileMaker Server 12.0v1.

                      • 8. Re: Portal filtering transfers a HUGE amount of data?
                        justinc

                        Some additional statements (from MightyData):

                         

                        12 Days of FileMaker 12 Bonus – FileMaker Server 12 Performance

                        FileMaker Server is the primary tool for monitoring and enhancing the performance of FileMaker solutions. FileMaker Server 12 provides new features to increase the ability of the database administrator or developer to create the best experience for the users. In the second bonus session of the 12 Days of FileMaker 12, I explore six aspects of performance with the new server.

                        Faster WAN Performance – FileMaker Server 12 can perform finds up to 40% faster, display large value lists quicker, and evaluate filtered portals on the server instead of the workstation.

                         

                        ...

                        • 9. Re: Portal filtering transfers a HUGE amount of data?
                          jbante

                          I imagine that the global field filters better via relationship than via portal filter because the relationship can take advantage of the database indexes, which the filter cannot do in general.

                           

                          The question shouldn't be "How often is it that you have a table with only a few dozen records in it?", but "How often is it that you have only a few dozen records related to the current record?", i.e. "How many records are likely to match in a portal relationship?", to which I answer: the overwhelming majority of the time. Not always, but most of the time. Looking at every record in an entire table in a production database is not what portals are designed for. If that's what you have in mind, you may need to re-think where portals fit in your solution.

                           

                          It may be inconvenient that we have to test code to see what it does and how well it does it, but that only puts FileMaker in the good company of every other programming tool and database engine on the planet.