5 Replies Latest reply on Nov 11, 2015 6:37 AM by philipHPG

    Portal vs List view performance

    philipHPG

      On one of my layouts I want to provide a list of upcoming due dates for an employee's active projects. There are three pertinent tables which are essentially as follows:

       

      Employees:

      ID

      Name

       

      Projects:

      ID

      ManagerID (links to Employees::ID)

      Name

      Status

       

      DueDates:

      ID

      ProjectID (links to Projects::ID)

      Name

      DueDate

      CompletedDate

       

      Initially I attempted to use a portal with filtered relationships on a layout based on Employees. I created an additional field in Employees called ActiveStatus that was a calculation (="Active"). Then I added that as a condition to the relationship with Projects (conditions: Employees::ActiveStatus=Projects::Status and Employees::ID=Projects::ManagerID). I then added two fields to Projects: Uncompleted, a calculation field (="x"); and TwoWeeks, an unstored calculation ( = Get ( CurrentDate ) + 14 ), and I created one additional field in Due Dates: CompletedDateX, a calculation field ( = CompletedDate & "x" ). Then, my relationship between Projects and Due Dates was as follows: DueDates::ProjectID=Projects::ID; DueDates::CompletedDateX=Projects::Uncompleted; and DueDates::DueDate<Projects::TwoWeeks. Using this I was able to get the data that I wanted in the portals. Yay!! ... Except that it was extremely slow in loading the portal, especially for remote users. The portal was set to sort by DueDates::DueDate and filtered based on the DueDates::Name, but even when I removed both of those, it was still quite slow. There are nearly 130,000 records in DueDates, but I thought the filtered relationships would make it more manageable (after all the filtering takes place, the largest result set is around 150 due dates for one employee's projects, but this took over 30 seconds to load and I'm working locally). I'm not sure why it is so slow.

       

      So, instead I ended up using a List view on DueDates, and did a scripted find, searching for DueDates::DueDate < Get ( CurrentDate ) + 14; DueDates::CompletedDate=""; Projects::Status=="Active" and Employees::Name==EmployeeName. This was much faster. More what I had expected. A few seconds at the most.

       

      But I'm still confused why the portal was so slow? Is it because the portal is working down: Employees -> Projects -> DueDates, whereas the List View is directly looking at DueDates? Or was there something about how I was setting up the filtered relationships that made it slow? (using the unstored calculation for TwoWeeks, for example)?

       

      Obviously there are significant advantages to using a portal in terms of layout control, but I wasn't aware there was such a significant performance hit.

       

      Oh yes, we are currently using FileMaker 11, but will likely be upgrading to FileMaker 14 in the next week or so. I'm not sure that I can expect a significant difference from that, but perhaps it's relevant.

       

      Thanks for your help,

       

      Philip

        • 1. Re: Portal vs List view performance
          Mike_Mitchell

          philipHPG wrote:

           

          ...

           

           

          But I'm still confused why the portal was so slow? Is it because the portal is working down: Employees -> Projects -> DueDates, whereas the List View is directly looking at DueDates?

           

           

          Hello, Philip. The second sentence I quoted is the reason, so you're right. Here's why:

           

          Your basic reason has to do with context, and how FileMaker has to fetch records over a network. Here's your Relationships Graph (as I understand it):

           

          Employees --< Projects --< DueDates

           

          Now, when FileMaker tries to resolve this, it has to say to the server, "Show me all the Projects related to the current Employee". That means they all have to download. Then, FileMaker says, "Wait! I also need to know what DueDates match the criteria on this relationship, because the filtering is dependent on that, too. So show me all the DueDate records that match any of the Project records I just pulled. Oh, and by the way, on each Projects record, you need to resolve this unindexed (unstored) calculation before you can figure out which DueDate records belong to it."

           

          Ugh.

           

          The difference when you go to the DueDates layout is, "Show me all the DueDates records that match my search criteria." That's it. So FileMaker only has to pull from one table, and do it on an indexed search. (Note: Even though the fields from Projects and Employees come from other tables, they can still be indexed, and therefore don't significantly impact performance.)

           

          Fundamentally, what you've (painfully) learned here is the value of field indexes. When FileMaker can index a field, performance skyrockets. When it can't ... well, not.  

           

          Hope that helps.

           

          Mike

          • 2. Re: Portal vs List view performance
            philipHPG

            Interesting.

             

            I understand that using unindexed fields (especially in finds or relationships) can be painful. I thought that would be mitigated by filtering down to an employee's active project first, but from you describe, it seems that FileMaker pulls everything, everywhere together first and then filters. I expected that FileMaker would use one relationship to limit the other relationship - find all active projects for the employee, and then only find due dates for those projects.

             

            Based on what you said, I took out the TwoWeeks unstored field from the filtered relationship and put that into the portal filter. It seems to work much better that way. Although, I'll have to ask someone who is working remotely to try it out.

            • 3. Re: Portal vs List view performance
              mardikennedy

              Mike's points prevail but also be aware that you'll find significant performance differences between FMP v11 and FMP v14.  Testing is critical.  Also, once you go to v14, you might find that the (newish) option - the ListOf Summary field which you can then combine with a global and build your portal with that - *might* be a good alternative.

              • 4. Re: Portal vs List view performance
                Mike_Mitchell

                Mardi makes a good point. You can, potentially, save yourself some aggravation by shortening the data model to this:

                 

                Employees --< DueDates

                 

                and using a return-delimited list of Project ID values that you assemble through scripting. This might avoid the extra record fetching (depending on how you go about it). For example, if you use ExecuteSQL to fetch the list of project IDs, so long as you don't have any records open on the client, the query will execute on the server - meaning no records have to be downloaded. All that's returned is the result, which will be WAY faster.

                 

                HTH

                 

                Mike

                • 5. Re: Portal vs List view performance
                  philipHPG

                  I'll be interested to see what the performance differences are. My impression has been that new releases are more about adding new features than changing how the underlying database works. I suppose they could have made some improvements to the optimizations. At this point, I'm keeping my expectations low, but hopefully will be surprised :-)

                   

                  That being said, the ListOf summary field, as you mentioned, seems like it has significant potential to change how tables are related, leading to improved performance, likewise with ExecuteSQL (although that was several versions ago - we're still getting there.)

                   

                  Thanks for your help and thoughts.