1 2 3 Previous Next 35 Replies Latest reply on Jan 23, 2015 8:39 AM by electon

    How to improve the overall performance - with portals and eSQL?

      Hi to all,

       

      in a recent discussion (How can I show only selectec data from a table with eSQL?) I learned that the "normal" usage of Filemaker can lead directly into performance problems. Meanwhile I also found and read a 30-page-document about the optimization of the performance of Filemaker  "design_performance_fm13_de.pdf" found via FileMaker Pro 13: The Missing Manual—New from O'Reilly Media).

       

      In my humble opinion this guide is a nice starter but counts too many pages for what it at the end really provides. There are more "hints" like "learn how the calculation engine works to increase your performance" instead of substantial material e.g. what I have to know about the calculation engine.

       

      Now I fear that my previous approachs to build Filemaker solutions were totally wrong designed as I use portals to show data from related tables etc. I also filter "searches" through theses portals (in the above mentioned thread I was already advised better to use eSQL because of performance probs I will run into).

       

      Out of the mentioned PDF-guide I learned that a portal that should only show a list of e.g. "company names" out of a table "Company" also loads ANY other information about all listed companies that is also in the table "Company" (like street, zip, country and in my case many other information). This can lead to heavy loads an slow down the solution.

       

      Now my opening question: how should I design my solutions the bets way to maintain a high performance? Silly) ieas that came to my mind after the last readings:

       

      • Shall I strip often used tables each into to parts - one with the often used fields in a search, the rest in a kind of a further information table? Like "Company Basiscs" and "Company information"? I do not like this idea at all because of doubeling the number of tables, increasing complexity and because I might not knoa which fields should go into the basic table and that my decisions might grow wrong after time.
      • Shall I not offering "full" listings like "all companies" because so much information will be transferred? Instead show only companies if a search is started? I fear that there are cases when I need "all companies".
      • Can I reduce the information transfered to a client while using an SQL-statement that only looks for a certain ID (e.g. SELECT id FROM companies THERE companyname LIKE '%search%' ") ? How can I show some more information (like city, country etc.) about each comany without loading every information of the listed companies? I recently found an example for Master-Detail-Layouts (http://www.modularfilemaker.org/module/masterdetail-2-0/)" but am not sure that I already understand everything.

       

      MaybeI wrote a bit too much but I would love to give you a good picture where I stand now - and would also love to learn as much as possible without stumble from each piece of knowldege to the next.

       

      Thanks too all,

      Luna

        • 1. Re: How to improve the overall performance - with portals and eSQL?
          siplus

          Hi Luna,

           

          performance problems is a very vague term. You can have a solution hosted on a $10k Mac Pro with 4 users or you can have the same database hosted on a mac mini with 25 users and WebDirect on your mind.

           

          One can argue that you should always do your best and think poor server, 100 users. While this might be philosophically correct, it could cut a bit your creativity's wings and the perceived functionality on the client's side. So the best thing is to see what the client wants, how his business will grow in the next 5 years and warn him about the possible trade-offs.

          • 2. Re: How to improve the overall performance - with portals and eSQL?
            wimdecorte

            Luna.media wrote:

             

             

            • Shall I not offering "full" listings like "all companies" because so much information will be transferred? Instead show only companies if a search is started? I fear that there are cases when I need "all companies".

             

            Certainly not in a portal.

            What are some of the cases where think you need to show "all companies"?  Typically it can be avoided by some so good UI and workflow.

            • 3. Re: How to improve the overall performance - with portals and eSQL?
              wimdecorte

              Luna.media wrote:

               

               

              • Can I reduce the information transfered to a client while using an SQL-statement that only looks for a certain ID (e.g. SELECT id FROM companies THERE companyname LIKE '%search%' ") ?

               

              To some extent but "LIKE" is a very expensive operator so test it carefully before diving in too deep.

               

              Keep in mind that there are may aspects to performance, not just the amount of data that is loaded.

              - unstored and summary fields can be expensive depending on the design and the # of records

              - stored calcs can be expensive during record creation

              - the UI design

              - the underlying design and graph management

              - don't forget the client's machine specs and the server deployment

              • 4. Re: How to improve the overall performance - with portals and eSQL?
                jlamprecht

                Furthering Wim's point here and including the fact you have stated you are filtering your portals, I would suggest changing your mindset. Instead of "filtering down" from all companies to a subset, try "filtering up" from an empty subset to a larger subset, such as Companies with the name "Company Inc."

                 

                Also, like Wim has said, watch out for LIKE as it can be quite a performance slugger if the criteria is too prevalent. If you can instead specify the criteria further into separate WHEREs or a single IN clause, that would be better.

                 

                If you must use LIKE clauses, please make use of the correct operators to to get the right results and also keep performance at a good level.

                 

                I hope this helps.

                • 5. Re: How to improve the overall performance - with portals and eSQL?

                  well, there are many as I have develope a CRM and production system. So the companies are needed everywhere like "Companies: add and edit" or "Invoices: show Invoices of..." or "Brands: manage brands of companies" etc.

                   

                  I think the prob is less the concrete use-case but more the technique. I often have something like the following:

                  • In different contexts I need a list of companies and that I also am able to search this list for a certain company
                  • There I want to add a new company or select a found company to a certain relation

                   

                  That it is.

                   

                  I also do not want to rely on "I have a fast server, good conections and only 10 users on current machines..." I really want to know how to design this the best way so that I do not have to redesign tables, schemes, UI and scripts in the future (too much...).

                  • 6. Re: How to improve the overall performance - with portals and eSQL?
                    wimdecorte

                    jlamprecht wrote:

                     

                    Furthering Wim's point here and including the fact you have stated you are filtering your portals, I would suggest changing your mindset.

                     

                    Very good point.  Luna: keep in mind that portal filtering happens on the client, so all the data for all the unfiltered records is sent to the client before the client decides what to show and what to hide.

                    • 7. Re: How to improve the overall performance - with portals and eSQL?
                      wimdecorte

                      None of what you describe though requires you to start with showing ALL companies...

                      • 8. Re: How to improve the overall performance - with portals and eSQL?

                        Sure, "like" is less performant than some other searches. But that is (here) not the prob so far.

                         

                        I also recognized that I will have to think about "calcs". Though I understand why they are "expensive" I am a bit frustrated that the feature of calculation seems to become "depreciated" to me as the can be such a performance malfactor. Sure there might be settings where calcs are not of any big costs. But it seems that I should avoid them if possible.

                         

                        I am also enhanceing more an more my solution by lightening the "graph management" as I learned that this also can "cost" and that there are easier ways - if I redesign my UI (not all information on one layout with the usage of many portals...) and if I integrate more eSQL-queries (because sometimes I can get rid of some relations).

                         

                        At the moment I want to understand whch techniques can help to avoid loading data of records that is not needed in a layout.

                        • 9. Re: How to improve the overall performance - with portals and eSQL?
                          DavidJondreau

                          It is helpful to know how FileMaker works so you can understand what techniques can optimize performance and the trade off of any technique. It's also important to recognize when "optimizations" aren't worth the trouble. In 80% of solutions where one technique may a fraction faster it simply doesn't matter.

                           

                          I don't split tables for performance reasons. I try not to have more than 50 fields or so because that's an indicator of bad design. Most entities don't have more than 50 attributes. The performance difference between loading 10 fields of a split table and 50 for an extra 10ms of load time per record simply isn't worth my time. The tradeoff is managing an extra table and relationship and then *longer* load times when accessing the other table through a relationship when needed.

                           

                          Also, keep in mind, a portal only loads 25 records at a time. So a portal showing all companies isn't pulling all 500 companies, just 25, until you start scrolling, and then it loads in 25 record chunks.

                           

                          I wouldn't list all companies though. Performance-wise it's not the greatest, but from a user interface standpoint, it doesn't work well. No user should have to scroll through more than 10-30 options or so, whether it's a value list, portal, or list view. A search box is a better option.

                           

                          Except in a few specialized cases, ExecuteSQL() will not increase performance. If improperly used, it will destroy it. When searching for information, FileMaker will use the index when it can. If you're doing a regular find on an indexable field, you won't be  "loading a whole record", just the index. The results of such a find will be "loaded", in 25 record chunks. ExecuteSQL will use the same index. Both regular finds and ExecuteSQL() will not use the index if using a "wildcard" search like and * in a find or LIKE in ExSQL(). Your example will be noticeablely slower than a regular find.

                           

                          In short, in my opinion:

                          Splitting attributes of a single entity into different tables for "loading" performance reasons (though there is some latitude in what qualifies as a single entity) is never worth it.

                          You will rarely see performance gains using ExecuteSQL().

                          There are sometimes clear ways to do things wrong but most often, performance tuning takes a lot of finesse.

                          • 10. Re: How to improve the overall performance - with portals and eSQL?
                            jlamprecht

                            For each of those contexts you just described, I would place under the Company table context, such that:

                             

                            Companies: add and edit


                            - Have a List view of all Companies. Make sure this is a thin layout as in don't put any unindexable fields on it, such as unstored calculations.


                            -When clicking on a Company, you go to a Company detail layout that you can edit the Company or else add a new one


                            Invoices: show Invoices of...


                            -For this one, I would add a portal onto the Company detail layout for all Invoices for the specific Company (Possibly even separate into Paid/Unpaid portals)


                            -If you do want a layout based on the Invoice table context, do not have a portal of companies. Simply, have the company the Invoice is linked to. You can use Find mode to find all the Invoices for the specific Company


                            Brands: manage brands of companies


                            -Same as above for Invoices. Add a portal to the Company detail layout (Use a Slide Panel/Tab Panel to separate into organized sections).


                            -Have a Brand layout that only shows a single company rather than all Companies.


                            These are just suggestions to the examples you gave. There are many ways to approach this. I hope this helps



                            • 11. Re: How to improve the overall performance - with portals and eSQL?

                              Dear Siplus,

                               

                              please have a look to my two following answers. It is not my intention to upgrade the computers/servers (as the setting is quite modern) and see already performance issues with one or few users. Therefore I fear about approaches that I use "out of the box" and how "right coding" should be adapted to Filemaker.

                              • 12. Re: How to improve the overall performance - with portals and eSQL?
                                Mike_Mitchell

                                "(in the above mentioned thread I was already advised better to use eSQL because of performance probs I will run into)."

                                 

                                Not actually true. The original thread asked the question about using ExecuteSQL. We never advised you it was faster than native FileMaker functionality.

                                 

                                ExecuteSQL can, in some cases, be faster. But usually not, because FileMaker has to translate the SQL query into its native query language to process the search. Whereas a native FileMaker Find or relational join uses that information natively.

                                 

                                ExecuteSQL is most useful when you don't have (and don't want to add) the necessary occurrences to your Graph to process the  queries you're running. But it's not a panacea for poor performance.

                                • 13. Re: How to improve the overall performance - with portals and eSQL?

                                  Dear Jlamprecht and Wimdecorte,

                                   

                                  yes, this is the point where I first saw the probs - therefore I changed my default filtering if no search string is present from "showing a record"(that means (the company names of) all records are shown) to "not showing a record". So loading the layout is less heavy.

                                   

                                  But I understood the underlying technique in Filemaker so that if I start filtering my companies with a certain search string (no, I also do not filter after each keystroke anymore because it is so slow...) even then all records have to be transfered to the client do be filtered and (worse) that this transfers contains all data of each record (and not only the company names).

                                   

                                  If all this is true it then seems to be a slow, bottlenecking technique. Therefore I asked for different approaches that are faster because the do transfer less data.

                                   

                                  Any ideas or did I undestand something wrong so far?

                                  • 14. Re: How to improve the overall performance - with portals and eSQL?

                                    Thanks,

                                     

                                    it all comes to split the layout differently than I did so far. Do not laugh but I started with a search field and portal of "found companies" and when I select one all company data is show as a firther portal in the layout - and als invoices and other relations are shown as portals 8using tabs to structure everthing nicely).

                                     

                                    This looks very compact and useful - but slows down already. To me ist seems that I have to switch to new layouts and switching between them while exploring data (I already fear this moment as everthing is already quite developed).

                                     

                                    One question to your sentence

                                    • "Make sure this is a thin layout as in don't put any unindexable fields on it, such as unstored calculations."

                                     

                                    Do I really have a faster layout if I just do not put fields with "unstored calculations" on it? Or do I have to avoid these fields totally (if the main goal is a faster perfromance)?

                                    1 2 3 Previous Next