12 Replies Latest reply on Nov 8, 2015 3:10 PM by CarlSchwarz

    Conditional value lists very slow. How about Portals?

    dyb

      I have 2 tables.

      Frist contains all the values (over 120000) which i'm using later in the second table.

      I have succesfully created a 3-lvl drill down setup so i can somehow navigate this.

       

      The problem is – it is not a reliable way to find values as user needs to memorize where are those values.

       

      I'd prefer to search for the values.

      I've seen a method with a web viewer where one could even search for the value name like in a regular search field (not just auto-completing values). I implemented that and it works but it's still very slow.

       

      I noticed that portals are super fast in viewing, what it seems, any amount of records.

       

       

      Is it possible to use a portal to search and enter a value?

        • 1. Re: Conditional value lists very slow. How about Portals?
          dyb

          I have implemented this method: http://www.modularfilemaker.org/module/ma-search-picker/

          As well as this one: http://filemakerinspirations.com/2010/10/dynamic-portal-filtering-while-you-type/

           

          Unfortunately both are quite slow – around 5-10seconds waiting.

          The one using portals is bit faster when scrolling and is more elegant to my taste.

           

          Is this perfectly fine with 120000 records?

          • 2. Re: Conditional value lists very slow. How about Portals?
            dyb

            I feel like i might've said something completely obvious and everyone's just slowly brings their palms forward their faces and sighs loudly...

             

            Well maybe the proper question here would be:

             

            How do i handle a problem where i need to pick values from a list of 120000 and drill down menus are not an options since they are not easy to navigate...

             

            Search is great but takes a lot of time...

            • 3. Re: Conditional value lists very slow. How about Portals?
              user19752

              If you need to download all 120000 records, it will be slow anyway at least first time. For fast getting, you need indexed find first, but it depends on what data you have and how can you narrow the data. If the data start with perfectly random alphabet, using 1st char to find make the data 1/26, but usually users don't have such data.

              • 4. Re: Conditional value lists very slow. How about Portals?
                okramis

                dyb schrieb:

                 

                I have 2 tables.

                Frist contains all the values (over 120000) which i'm using later in the second table.

                I have succesfully created a 3-lvl drill down setup so i can somehow navigate this.

                 

                The problem is – it is not a reliable way to find values as user needs to memorize where are those values.

                 

                I'd prefer to search for the values.

                I've seen a method with a web viewer where one could even search for the value name like in a regular search field (not just auto-completing values). I implemented that and it works but it's still very slow.

                 

                I noticed that portals are super fast in viewing, what it seems, any amount of records.

                 

                 

                Is it possible to use a portal to search and enter a value?SQL-PORTAL.png

                I implemented a portal which gets its records by a mult-line-key, filled through a dynamic sql statement. First criteria is a like operator in name string and two more who can be assigned to fields like id, type... It works great, but I have no idea, how it would perform with > 120000 records.

                • 5. Re: Conditional value lists very slow. How about Portals?
                  dyb

                  Looks neat okramis, i'd take a closer look although i have no idea about sql.

                   

                   

                  I only get into more problems because of this huge value list... I used a technique with a pop-up menu over the drop down menu to store id and view another field data. It made browsing through records very slow with aprox. the same 5-10 sec delay. I fixed that by replacing the pop-up menu with a simple edit menu to only display the related value.

                   

                  What i found out so far:

                   

                  – overall performance (like viewing records, changing layouts) is only affected when there's a long value list and it's being loaded.

                  – reducing the value list with drill down approach is by far the best solution in terms of improving performance, but my lists are hard to navigate because they are not known to the user that's picking the values (like you were trying to pick a city but you don't know i which country it is...)

                   

                   

                  – pop-up menu over drop down trick with a value list this long is causing delay

                  – using pop-up menu with a value list this long causes filemaker to crash

                  – viewing all the records in a portal is instant

                  – filtering portals though creates the same delay as value lists

                   

                  – using regular filemaker find mode works instantly

                   

                   

                  Maybe the answer lies in find mode.

                  Please advise on how do i bite on this:

                   

                  1. User types in a "search" field in browse mode and hits enter.

                  2. Go to find mode to a chosen layout, perform find on a chosen field and a found set is a result.

                  3. The values (id's) from that found set are copied to a global field "list".

                  4. Go to browse mode and to the original layout.

                  5. Use the global field "list" as a value list and display related values (name).

                  6. Click and set the value of the field to value id.

                   

                  It looks similar to what's happening in portal filtering but i want to set filtered records BEFORE even viewing the list because the portal is slow if you filter 120000 records.

                   

                  How can i achieve this?

                  • 6. Re: Conditional value lists very slow. How about Portals?
                    CarlSchwarz

                    I'd like to know more about what you are searching on.  I had a table with 240000000 records in it and finding a single record over WAN was instant.

                    If the field that you are doing a search on is "Indexed" and no calculations are involved then a search will be instant.

                    SQL will only perform as well as native Filemaker too, so if you do use SQL then only SELECT indexed fields.

                    • 7. Re: Conditional value lists very slow. How about Portals?
                      dyb

                      That is comforting! Well then i guess i have to learn SQL

                       

                      I'm searching through Unicode Characters specifically their names. (imported from Unicode Character Database – UCD)

                      Using value lists or filtering portals with the method described in the link in my previous post is slow. Only narrowing the value list and a portal with all the records works instantly. I even tried to filter the portal with a simple (not equal "banana") calc and it's also slow.

                       

                      Index is turned on and FileMaker own Find Mode works instantly so i though i could use that to my advantage.

                      If you say SQL performs as fast as native find then maybe that is an option but what should i do with that?

                      Are you talking about the same technique as okramis?

                       

                      I really feel the need to dig into the ExecuteSQL... Any good place to start?

                      • 8. Re: Conditional value lists very slow. How about Portals?
                        okramis

                        Looks neat okramis, i'd take a closer look although i have no idea about sql.

                        You don't have to do it by sql. You could collect the ids by "conventional" search (summary field "list of") and set the global multiline keyfield with the collection.

                        What i found out so far:

                         

                        – overall performance (like viewing records, changing layouts) is only affected when there's a long value list and it's being loaded.

                        – reducing the value list with drill down approach is by far the best solution in terms of improving performance, but my lists are hard to navigate because they are not known to the user that's picking the values (like you were trying to pick a city but you don't know i which country it is...)

                         

                         

                        – pop-up menu over drop down trick with a value list this long is causing delay

                        – using pop-up menu with a value list this long causes filemaker to crash

                        – viewing all the records in a portal is instant

                        – filtering portals though creates the same delay as value lists

                        Yes, filtering Portals is nice with a small recordset, but gets slow with larger, because it has to be done all by the client, all records have to be transferred from server to client.

                        • 9. Re: Conditional value lists very slow. How about Portals?
                          dyb

                          Thank you okramis and CarlSchwarz! Your suggestions were very helpfull. I read a few articles about this method and found this video which was the most helpful: Portal Filtering with ExecuteSQL - ISO FileMaker Magazine

                           

                          I implemented this method and now have searches that take usually take 1-2 seconds but if there are many results it takes much longer. I believe this is due to the way this search is performed ('%~searchstring%'). I guess i will try to limit the results to around 100 – that should give me constant search speed... i guess...

                          • 10. Re: Conditional value lists very slow. How about Portals?
                            CarlSchwarz

                            Actually I never use SQL, I use native Filemaker finds, or traverse the relational structure.  I have worked on other databases where people do use the SQL though and from that experience I know that the execute sql commands work  at roughly the same speed as doing it in Filemaker.

                            • 11. Re: Conditional value lists very slow. How about Portals?
                              CarlSchwarz

                              I just found out that ExecuteSQL does have a little performance hit compared to doing operations in pure Filemaker.  I don't know the hit but if you are after more performance it might be worth trying to do it in a native way.

                              • 12. Re: Conditional value lists very slow. How about Portals?
                                CarlSchwarz

                                Here are some resources thanks to forum regular Beverly Voth (just passing it on)

                                 

                                get the pdf and sample files! there are links to other sources as well

                                     http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

                                 

                                for info on newer functions and other articles:

                                     http://www.seedcode.com/tag/filemaker-sql/

                                 

                                for other articles not listed the first link:

                                     http://filemakerhacks.com/category/executesql/

                                 

                                for a forum just about FQL (FileMaker Query Language):

                                     http://fmforums.com/forum/155-filemaker-query-language-or-fql/