8 Replies Latest reply on May 2, 2017 12:06 PM by JamesPeragine

    Setting up relationships for optimum performance

    JamesPeragine

      I'm setting up a new portal that filters companies based on criteria. I would like to get some advise on how to set this up with optimum performance (fastest speed and least strain on the system) 

       

      Is it best to use a filtered portal or to filter through relationship?

       

      Also, If I need to filter the list by the States that a company services what is the best way to set this up using a relationship?

       

      Should I match a field that contains all the states that the company services and relate it to a calculation field that has all of the states in a list?

       

       

        • 1. Re: Setting up relationships for optimum performance
          DavidJondreau

          Relationships will almost always be faster than filtering portals. When the number of records gets below 20 or so, the difference may not be noticeable. And depending on the child record, you may not be able to use a relationship for certain criteria (unindexable fields).

           

          If doing a 'live' filtering system, say a type ahead that winnows down a list, you will run into an issue with refreshing the relationship.

           

          It's hard to give specific advice without know more about your current system and your desired result.

           

          It sounds like the idea might be to have a box in which the user types the two letter abbreviation of a state, hits enter, then sees a list of companies for that state. Is that correct?

           

          If so, then you can create a relationship between your current table and companies based on a calculated field in the current table and a calculated field in the companies table. The calculated field in the current table will determine if any data has been entered into the "box" (a global field). If so, return that text, otherwise return 1. The calculated field in the companies table will be = "Companies::State¶1". That saves you from having to create a calculated field with all the state names, while still showing all the companies if nothing has been entered.

           

          There are other ways to achieve the same ends.

          1 of 1 people found this helpful
          • 2. Re: Setting up relationships for optimum performance
            siplus

            David Jondreau wrote:

            The calculated field in the current table will determine if any data has been entered into the "box" (a global field). If so, return that text, otherwise return 1. The calculated field in the companies table will be = "Companies::State¶1".

             

            I always loved this trick

             

             

            Back to the original question:

             

            if you filter the portal at layout level, all matching data from the relationship will be retrieved by the server and sent down to your client, which will do the filtering locally.

            1 of 1 people found this helpful
            • 3. Re: Setting up relationships for optimum performance
              JamesPeragine

              Thanks for the feedback.  I always get confused by this.  I thought that you couldn't relate tables between two calculation fields?  Now I'm remembering that you can do it if they are indexed right?  But is there any issues with the fields being updated if the user changes them?

               

              In this case the relationships will be static.   I have customers that are located in a specific State.  I need to list all of the Providers that service that customers State.

               

              What I currently have is

               

              Customers::State <-> Providers::StatesServiced  (in a list)

               

              I was just wondering if this is the most efficient way of doing this performance-wise and if there might be any Jedi tricks (like the one above) that can make this even more efficient?

               

              Back to the original question:

               

              if you filter the portal at layout level, all matching data from the relationship will be retrieved by the server and sent down to your client, which will do the filtering locally.

               


               

              Got it.  Filtered portals are definitely not the way to go with these then

              • 4. Re: Setting up relationships for optimum performance
                DavidJondreau

                You can use calculation fields for relationships. What you can't do is have a relationship resolve when you use an unindexable field (usu. an *unstored* calculation field) on the *child* side of the relationship.

                 

                Based on your new information, it sounds like you've got the relationship configured correctly.

                1 of 1 people found this helpful
                • 5. Re: Setting up relationships for optimum performance
                  philmodjunk

                  Using calculation fields can also affect portal behavior options. You can't use the "allow creation with blank add row" to add new portal records if the portal's match field is a calculation field as the calculation field is not modifiable.

                  1 of 1 people found this helpful
                  • 6. Re: Setting up relationships for optimum performance
                    JamesPeragine

                    Got it and this is very helpful.   Does adding Calculation fields add additional load of processing on the system?  I've been doing everything to avoid using them as I intend to scale my hosted solution to a lot of users. 

                    • 7. Re: Setting up relationships for optimum performance
                      philmodjunk

                      As a general rule: STORED and AUTO-ENTERED calculations do not. The exception is when doing a batch update of many records on a field referenced by the calculation or when importing large numbers of records into the table. Then the fields have to re-evaluate in order to store new values. Plus, any associated index undergoes a major update at the same tiem.

                       

                      UNSTORED calculations, on the other hand, only evaluate "when needed". This avoids the issues listed above, but can cause issues when you display multiple records with this field included or when performing searches, sorts, and portal filters that refer to them.

                      1 of 1 people found this helpful
                      • 8. Re: Setting up relationships for optimum performance
                        JamesPeragine

                        Got it,  so I'll be a little less afraid of using Calculation fields now that I have a better idea of the context in which to use them.   I never fully understood how they effected performance.  This is very enlightening.

                         

                        Thank you so much for the feedback!