12 Replies Latest reply on Jul 12, 2017 9:42 AM by petery009

    Drop Down List Load is Slow?

    petery009

      Hi Everyone

      There's a simple value list of a field that contains more than 50,000 records.

      When user click a drop down list that using this value list in very first time the system started, sometimes it took a couple of minutes. (This is done on Azure server with not bad internet connection). After the load of the list is not bad.

      Mostly like to know why it happens? and also is there a way to work around this? like doing something in start up script? waiting in the start up of the system is ok.

      Any idea is appreciated.

      Thanks!

      -Peter

        • 1. Re: Drop Down List Load is Slow?
          philmodjunk

          You probably have a very large index for the field that supplies values to that value list that has to load onto the client before you can use it.

          • 2. Re: Drop Down List Load is Slow?
            FabriceNordmann

            Is the value list a 1-field or 2-field?

            is it showing all values or only related ones?

            is it slow when you're in full access?

            • 3. Re: Drop Down List Load is Slow?
              Mike_Mitchell

              What you're seeing is the latency delay associated with the first time FileMaker loads data from the server. FileMaker caches the records it needs locally, but it has to load them to the client the first time.

               

              The best thing you can do is narrow the table involved. Cut down on the number of  fields in the table as much as you can. FileMaker loads the entire record, so the less it has to load, the faster it will go.

               

              Other options include using a one-to-one relationship with the frequently used fields in one table and the rarely used fields in another and using a server close to the end user (and avoiding using the cell network).

               

              HTH

               

              Mike

              • 4. Re: Drop Down List Load is Slow?
                petery009

                That make sense to me, if so , how do i get that pass to client in start up , not when user click the drop down list ?

                Thanks.

                • 5. Re: Drop Down List Load is Slow?
                  petery009

                  Is the value list a 1-field or 2-field?

                  >> 2 fields, only show the second one, the first one is system id.

                  is it showing all values or only related ones?

                  >> show all values

                  is it slow when you're in full access?

                  >> yes , it's slow in full access.

                   

                  Thank you!

                  • 6. Re: Drop Down List Load is Slow?
                    philmodjunk

                    If the records in that table are unique, you are pulling 50,000 items from the server for your value list even if the table is very narrow.

                     

                    There are several work arounds that you can play with:

                    If you can organize this data into categories--even if a given record might need to be a member of more than one category, you can set up a conditional value list where you select the category first, then open the value list to get a list of just the values that are a member of that category.

                     

                    You might choose not to use a value list at all. Instead have the user enter search text and display a list of values matching the search text when they click a button after entering the search text. (Yes, you can also use script triggers to update such a list one keystroke at a time, but it might be too slow so try the button option first and then you have a fall back if a script trigger based key by key method turns out to be too slow.)

                    • 7. Re: Drop Down List Load is Slow?
                      Mike_Mitchell

                      A value list of 50,000 items is likely to be less than ideally useful. Agree with the recommendation to use a search / select paradigm.

                       

                      Nevertheless, general performance principles still apply and can improve the user experience.

                      • 8. Re: Drop Down List Load is Slow?
                        philmodjunk

                        I don't disagree with the basic principle of narrow tables improving performance, I'm just not convinced that it's a factor in a delay that only happens when the user deploys the value list for the first time. I could easily be wrong, but suspect that the client has to pull a major chunk of data down from the server at that point in order to get an up to date index of that particular field in order to deploy the value list.

                        • 9. Re: Drop Down List Load is Slow?
                          Mike_Mitchell

                          The question of whether the index loads separately is an interesting one, but I've never heard of the index coming down separately from the records. Again, could be wrong, but the behavior the OP is describing is consistent with the record caching behavior we've all heard about.

                          • 10. Re: Drop Down List Load is Slow?
                            philmodjunk

                            It's an interesting question, but to me it's not quite the same. If you put a field on a layout, we know that we get all the entire record, all the other fields from that record as well as that one field. But in this case we are simply popping open a value list, The values that make up that list aren't necessarily associated with any one record in the table that supplies those values to the value list. there could be multiple records with a value that matches to one value in that value list, so I can't quite see displaying the values of the value list as being affected by the width of the table. That implies having to download the entire table just to open a value list.

                            • 11. Re: Drop Down List Load is Slow?
                              FabriceNordmann

                              Because it's a 2-field value list, the client has to download data (would it be a 1-field value list, only the index would be required, which would be a snap).

                              As Mike said, it's important that the weight of data to be downloaded is not too high. You should narrow the table and maybe more importantly ensure that fields containing large amount of data (long texts) are stored in a related table.

                              Another approach is to make this list a single field (the field that is currently your second), create an input field displayed on the layout (with the value list), and make your current input field a lookup.

                              • 12. Re: Drop Down List Load is Slow?
                                petery009

                                Thank you all ! Appreciated !

                                I will take some time to think of what you guys suggested and ideas.

                                If i get anything good, i will post back and share it.

                                -Peter