9 Replies Latest reply on Sep 26, 2012 1:23 AM by jrenfrew

    SPEED, speed, Speed AND SPEED

    Vincent_L

      The first thing that will make me quit the plattform is the Filemaker's poor performances.

      Filemaker needs a deep rework of it's internal engine with order of magnitude speed increase. No, I'm not trying to compete with MySQL for web hosting. Just the speed of my 100% FMP based solution.

       

      So when Filemaker will be built with speed in mind ?

       

      - The related fields are too slow. One to one relationship should be very close to as fast as if the related field would be in the same table. Filemaker needs more clever related filed fetching like MySQL does with its  "explain". For instance, the order of the relationship matching fields declaration shouldn't matter, Filemaker's should optimize it itself (and it coudl be done one a day, with a server command "optimize solution", that would built the necessary optimization trees, so that optimization won't impeed live performance)

       

      - The unstored calc are way too slow (a best practice is to avoid them, but doing so negates the purpose of a related database, and negates Filemaker rapid development feature).

      I'm pretty sure unstored calcs are re-eavaluated too much time : if you've unstored field C, based upon unstored field B which is based upon unstored field A, A will be evaluated 3 times instead of one.

      There should be some sort of instant caching.

      Also if in the same calc you write something ling related::field1 + related::field1  + related::field1, related::field1 will be evaluated 3 times instead of one, of course you can fix it by using a let function.

      But that shouldn't be necessary, especailly for newbies.

       

      What I thing would help is a kind of automatic let function : whenever a Record is displayed, all unstored calcs involved in that display, would be evaluated and cached just for the time of the display, using a

      an order tree that Filemaker would build when FMP structure is updated. So in the previous example, Filemaker would know it has to evaluate A first, cache it's value in ram, and then on subsecquent calls get that cached value.

       

      - Often, unstored calc are just depending on related static values that don't change often, so maybe we could have an auto-re-evaluation of the depended fields whenever a used field is changed. So in my previous example, changing the value of A, will automatically trigger the update of field B and C and those would behave as if they where stored. I know that some advanced Filemaker user use that techniqu

      will script tiggers but that's a nightmare to implement and maintain, and it can be slow if many fields and records depends on one, but that slowness is mostly due to the fact it's not native.

       

      - Imports and exports are too slow

       

      - Deletes are too slow. Also we should be able to truncate a table instantly (actualy I've a script that takes 1 hour on a powerfull SSD server just to delete all record of a table).

       

      - Replaces are way too slow (and we need to be able to replace multiple fields values in the same replace). It's often faster to export and import rather than use the replace function if you need to change any fields.

       

      - Scrolling list view and table view is too slow (not only the 12's bug, but whenever there's related fields / unstored calc fields they become unusable)

       

      - Finds involving unstored fields are unnecesserally too slow and this is something that could even be fixed in 12vX point release : Let's do a search with 3 fields involved Store Field A1, Store Field B1 and unstored field C1. Filemaker will evaluated all the fields C of all the records of the database, even though A1 and B1 criteria alone would result in only 3 records. Experienced developpers will of course do the search in 2 steps, search for A1 and B1 and then constrain the search on C1. That will be massively faster. It can be scripted, so why Filemaker doesn't do it by itself ? Especillay for newbies and expecillay since the quickfind feature appeared.

      Of course Filemaker internally could even do better in FMP 13 : look with of the indexed field has the least record, and then optimize it that way : There's 400 records with A1 corrsponding to the search criteria, and only 3 corrsponding to B1. So Filemaker will do 3 search, B1 then constrain on A1 and then on C1. It could do that optimization on the fly when in find mode, or, even better for scripted search it could use search strategy tree that would update once per day with the aformentionned "optimize solution" feature, that would count the different record counts for the different scripted criterias (of course it would ignore criterias involving a variable).

       

      - Freeze window and blank form view shouln't be optimization : A filemaker trick to get non terrible performance is to switch to a a blank for view layout and use the freeze window script step, that shouldn't be the case. All script should be computed off screen, display shouldn't be involved at all, of course except if there's a refresh window display step. That's a trap for newbies, and it litters the code of developpers. So All script should be computed off scren unless a Refresh display window step would be triggered. That means eggting rid of old filemaker depencies that involve the display which will be very good for server side processing anyway.

       

      - Much more should be done on the server : At least all scripts steps that are actually writing to the database, should be performed by the server automatically. For instance if a client triggers a replace, the replace should actulaly be done on the server automatically without a line of code specifiying it. New records, set fields, imports, Execute SQL (when we'll get the much needed write function).

      Of course, searchs, Execute SQL Reads (like select)  and maybe sorts. But for the read parts maybe operation that do not Extend the found set will be best performed by the client (for instance sorts, and constraining searchs). The marvelous FMSDIFM http://www.1-more-thing.com/FileMaker-12-Video-FMSDIFM.html shouldn't need to exists. It should be the native way.

       

      - Much better WAN performance (this will imprive LAN also) : of course the previous point helps, but that also means much less chatting between FMS an client. Maybe it's time that when loading a record only the necessary fields gets loaded and not all the fileds of the record (here again my optimize solution tree could help).
      But I also thing that the way FMS and client are chatting can be improved : let's say that FM Client request a given record with 20 fields, it seems to me that the client will issue 20 requests, wich will cause 40 packets of data to be exchanged (20 to ask for them 20 for getting them). I'd suggest that the client would ask the server : give me the fileds I want, 1 request, FMS will compute whats needed (maybe using my optimation tree), and make a big chunk of data and push it to the client. 2 exchange of a big data chunk.

      Maybe I'm wrong  and it's already the case for each records, but at least it seems that if FM Clienst ask for 30 records, theres will be 30 request nowadays, while there' could be one : please FMS give me record 1,2,8,9,456…470 and then the server would compute the data that ,need to be send, it will divide it but the chunk size and number given the connection type, and then send it to the client.

      The idea is to lower the number of request and hence packets exchanged, because that's the request access time that kills the performance. For instance I've never hit the 2MB/s throughput between my high speed client and server on Gigabit LAN, that's because there's plenty of requests exchanged (I don't use container fields, only text/number and calc fileds, I gues one can hit it with big container fileds).

      The need to lower the network packet exchanged is especially more inmportand with Wifi GO, because Wifi has much slower access time than LAN.

       

      - Layout Rendering speed in Form view, but especially in list and table view : In 2012, all the drwing should be instant, scrolling should be smooth as butter. I'm just talking about 100% indexed flat data, just considering the drawing part. To those who think a filemaker layout is complex, I say no, much much less than most webpages (remeber i'm talking about indexed flat data), we are in 2012, my 2010 iPhone 4's safari blows FMP 12 layout speed rendering. So clearly there's huge optimization to be done, especialy in list and table view, where even with latest improvments it hinder usage.

      Maybe the Display Rendering could be done in a second thread for another core, nowadays most computers have more than one core, right now they seit iddle with Filemaker

       

      Modified 2012-09-26 : Added last line about layout rendering by second cpu core

        • 1. Re: SPEED, speed, Speed AND SPEED
          jbante

          If performance were everything, all code would be written in assembly. FileMaker sells itself as a tool for rapid development more than a tool for performant deployed apps — which is not to say that performance can't or shouldn't be improved. If I can eat my cake and have it, too, I'll take it; but I acknowledge that there are other priorities, and that other organizations order their priorities differently.

           

          There's one part of your post I'd like to reinforce: The evaluation of calculations that occurs when a layout renders can be mysterious and difficult to optimize for. More clarity and documentation on the order of calculation evaluation while a layout renders and what events trigger re-evaluation would be helpful. (Though I understand if FileMaker would not want to release such details for fear of being locked-in to a particular behavior in future versions because developers start writing apps that depend on very granular particulars.) Control over this sort of thing might be a useful feature for a future version of Advanced.

           

          I get that everyone wants FileMaker to be faster, but I don't often hear anyone complaining about performance say how fast FileMaker would have to be to be satisfactory. Asking for anything to be truly "instant" is ignorant of physics. Asking for the results of operations to render at the pace of human perception is slightly more realistic, but is that always reasonable when it takes at least several hundred milliseconds for a human to have an informed reaction to an event? Most of the performance improvements of modern hardware have gone to asking our computers to do more — like doing interpreted evaluation of hot-swappable code in a rapid development environment while hiding the complexity of the software stack — rather than to accomplish the same results faster. I'm simultaneously reminded of the days when a 56K modem represented above-average performance, and of something John Siracusa said a couple months ago to the effect of, "There's no such thing as a fast-enough computer." I'm OK with holding the performance of FileMaker applications to a high standard, but only if that standard is measurably defined, and defined in terms of results rather than methods.

          • 2. Re: SPEED, speed, Speed AND SPEED
            jormond

            jbante wrote:

             

            Most of the performance improvements of modern hardware have gone to asking our computers to do more — like doing interpreted evaluation of hot-swappable code in a rapid development environment while hiding the complexity of the software stack — rather than to accomplish the same results faster.

            This is definitely a deep subject that most, even high level developers miss. I recently read a post where someone wanted FileMaker to be as fast as Google Chrome's suggested search results.  Errr... that would be nice. But isn't going to happen.

             

            There is a difference between hard-coded data and nested dependency code. The easiest example is Microsoft Excel. 

            • A typed value displays the fastest and has the least impact on performance.
            • A calculation local to the worksheet is really fast.  Until you get about 100,000 calculations on the worksheet.
            • A calculation that references data in another workbook, is usually fast, but there is performance hit.  Mostly, you won't perceive the difference.
            • A nested calculation that references another calculation that references a calculated array, can be fast but you may see a slight delay.
            • A hundred nested calculations that references another calculation that references a calculated array, can be fast but you will see a delay on most current computers.  Unless you are using a 6-core/3.0Ghz, 10 GB workhorse ( then you never see the performance hit that someone with a 2.0 Ghz, 2GB workstation will see ).

             

            Even if everything is fast, the more dependency, the longer it takes for the application to run through everything to return the result.  FileMaker is more like the last one.  Not that it is slow most of the time, but it is substituting your data - structure - calculations into the application code, running all the dependencies to evaluate the actual calculation, then calculating to return the result.  It all takes time.  And if you throw hundreds of records at the host, it has to run all the dependencies over and over.  A mega-server will do it faster, but you are still limited by the network, and client machine. I've run into slow downs, but it's usually because I am doing something that I shouldn't...or was being lazy and ended up forcing FM to evaluate 100k records to give me the number I wanted instead of doing it right and thinking through a better way to program.

            • 3. Re: SPEED, speed, Speed AND SPEED

              Out of all the things you posted, if I had to pick only one thing on the list it would indeed be better handling of server / client. With more concentration on WAN and mobile devices, any optimization of what the server could handle would be great. For example, handling aggretates with the ExecuteSQL function on ther server or bringing only specific data down from the server much like how its handled via ODBC would be fantastic. Although historically the client handles so much, perhaps it may be time for FMI to reevaluate what happens when a server gets involved.

              • 4. Re: SPEED, speed, Speed AND SPEED
                wsvp

                +1

                 

                While I too would like to see improvements in FM performance, However I would not want to see them at the expense of reliability.  While a developer can greatly effect performance by using a more efficient design approach, there are some areas where FileMaker may not be the right tool.  While there are many limitations to FM, there are also many unique advantages to it.  I suspect there are many people here who have evaluated other solutions "as I have", only to find that the grass is NOT always greener on the other side of the hill.

                 

                To me FileMaker is a lot more than just a database, I feel it is it's own unique development environment.  It has a very scalable approach to user friendliness.  A raw beginner can build a simple roledex, or an advanced developer can build a solution that can manage the operations and finances of multi-million dollar companies.

                • 5. Re: SPEED, speed, Speed AND SPEED
                  taylorsharpe

                  I really love the features of FileMaker and layout concept for reports and input screens.  The fact that the data is live and dynamic is very unique and a primary selling point for many of my customers.  But I will acknowledge that I have 3 big customers that converted from 11 to 12 and they have been very vocal and critical as to the performance hit.  I've been doing things to improve on it, but it has been my number 1 client complaint about 12.  All 3 have asked about going back to 11, but I have dissuaded them.  Still, it is something that clients are noticing.  They love the features, but equally want the speed.  And I'm hoping FileMaker is already working on this issue in various ways.  I love FileMaker, but 12 has been a challenge in that I have had to spend more time optimizing than I have in the past. 

                  • 6. Re: SPEED, speed, Speed AND SPEED
                    vince.menanno

                    Vincent,

                     

                    FileMaker is a huge product in terms of all the capabilites available. And we all have things we would love to see. And I agree with you on the subject of performance. If there was one single thing I could ask for in the next update it would be to focus on performance as much as possible. So that there could be some noticable difference. I know I recently struggled with something that was really peforming poorly even on a LAN. I was stunned. Normally when I walk into a project I try to get a sense as to the amount of data that they will be working with, the number of users and the distances involved and try to set expectations. And I tought on this recent project FileMaker would be a good fit for it. As it turns out I have to really work hard to keep the performance accecptable. I guess the most furstrating thing is once you get yourself into a performance corner. We really don't have any tools to get a solid understanding as to what is going on. I wish there was some console feature of FileMaker for Advanced users... that we coudl turn on and see what is going on. This way at least we could see where the problem spots are instead of guessing.

                    • 7. Re: SPEED, speed, Speed AND SPEED
                      mbraendle

                      Vincent_L wrote:

                       

                      - Finds involving unstored fields are unnecesserally too slow and this is something that could even be fixed in 12vX point release : Let's do a search with 3 fields involved Store Field A1, Store Field B1 and unstored field C1. Filemaker will evaluated all the fields C of all the records of the database, even though A1 and B1 criteria alone would result in only 3 records. Experienced developpers will of course do the search in 2 steps, search for A1 and B1 and then constrain the search on C1. That will be massively faster. It can be scripted, so why Filemaker doesn't do it by itself ? Especillay for newbies and expecillay since the quickfind feature appeared.

                      Of course Filemaker internally could even do better in FMP 13 : look with of the indexed field has the least record, and then optimize it that way : There's 400 records with A1 corrsponding to the search criteria, and only 3 corrsponding to B1. So Filemaker will do 3 search, B1 then constrain on A1 and then on C1. It could do that optimization on the fly when in find mode, or, even better for scripted search it could use search strategy tree that would update once per day with the aformentionned "optimize solution" feature, that would count the different record counts for the different scripted criterias (of course it would ignore criterias involving a variable).

                       

                       

                      Sequential searching may have drawbacks if one or several of the results sets get very large and must be kept in memory for the final combination of the result sets. Especially this may be the case when right truncation - which is standard for text search in FileMaker - is used and a large number of records match. As we all know, FileMaker Inc. has found a very clever way to deal with this and to always return an answer, whereas other databases such as Oracle may run into limits and return no answer although there should be.

                       

                      Let me demonstrate that on the example of our library system that is FileMaker based, and on the system of the main library, that is a standard library solution based on Oracle on a large multiprocessor server (Sun system).

                       

                      Our library system: http://www.clicaps.ethz.ch/en/

                       

                      Do a find on

                       

                      J Am Chem Soc

                       

                      Answer will be returned in 1-2 seconds (and actually, 1 commit and 4 searches in 3 tables had been done in the background in the meanwhile).

                       

                      Main library system:

                       

                      http://opac.nebis.ch/F?local_base=nebis&CON_LNG=ENG&func=file&file_name=find-b

                       

                      In Keyword Search field, enter:

                       

                      J? Am? Chem? Soc?

                       

                      which is equivalent to the query above.

                       

                      You will wait about 20-30 seconds, after which the system tells you that it found too many entries. In earlier times, you saw that this was because of the J? subquery which yielded several 100'000 records and exceeded the limits.

                       

                      Well, and that does not depend on the size of the database, although I have to admit that the main library system has way more records than our system. On a larger FileMaker DB (24 GB, 6 GB of pure text and 18 GB of index) which is probably comparable to size of the main library system, FileMaker still returns 200'000 records with the same query as above in 4-5 seconds. The letter J even matches several times within the record; on average, this database has about 500 words per record.

                       

                      This lets me assume that FileMaker Inc. has already heavily optimized their search algorithm, and probably have implemented similar tricks as you have described.

                      • 8. Re: SPEED, speed, Speed AND SPEED
                        Vincent_L

                        Martin,

                         

                        My search example involves several fields. Searching indexed fields firtst, constraining to unstored one after will always be faster than evaluating all the unstored records of the DB. That's a very easy feature to implement, heck we could event script it.

                         

                        What you describe seems to me as text search in one field, and based on your description I would think that your main library systems doesn't index words as filemaker does hence the filemaker speed.

                        • 9. Re: SPEED, speed, Speed AND SPEED
                          jrenfrew

                          So maybe an analysis tool which logs how many calls, how much data, latency and speeed of drawing would be useful addition, then Vince could write us a tool to analyse it...