11 Replies Latest reply on Jul 31, 2013 12:27 AM by davehob

    FileMaker database slow when accessed via internet/remotely

    user16773

      Hello,

       

      We have developed a solution using FileMaker 12 for Optical store. The solution consists of Customer, Order, Inventory handling and operations based on that. The problem we are facing is speed of the application. When accessed the solution locally it works fine but when accessed via internet the solution is taking time. The solution is hosted on FileMaker server 12 and users are accessing it remotely via WAN from various store branches.

       

      We have operations as simple as List view to Detail view and as complex as Reports including many Summary fields. For simple operations, it is taking time (14 sec) to go from List View to Detail view as compared to when accessed locally. After we access from List view to Detail view, second time it does not take much time.

       

      We have bypassed FileMakers login screen and we have an opening script. When we keep the script debugger on, it takes around 1.19 mins to go to that opening script.

       

      For complex reports with Summary fields it takes around 7-8 minutes to load that report. This is the main issue we are facing. This report is based on ‘Order’ table and grouped as per ‘Order Date’. The data to be displayed comes from two different child tables (‘Order Details’ and ‘Payments’). For this we have created few calculations fields using sum function in order table. These calculation fields are further used in summary fields.

       

      Can anyone please help us with this speed issue?

        • 1. Re: FileMaker database slow when accessed via internet/remotely
          rurounigatts

          I've had some similar speed issues and I've discovered the following points on speeding up:

           

          1)  I had found a logic error where records were being added but not all related records were being deleted due to an incorrect setup.  This caused a buildup of "useless" data that had a major impact on speed because it has to search through all those records on top of what it was supposed to do. (I had found 600,000 "orphaned".  Deleting the "orphan" data did in fact speed it up).  Ensuring the table structure and logic goes a long way for designing the most efficient database you can.  But database search times will increase in relation to the increase in database size.

           

          2)  Try to limit the amount of scripts you run on a layout.  The more scripts that are on a layout, the more processing that has to be done and could cause a hold up.  I've found this was amplified when using 3G.  So it is important on limiting the amount and size of the scripts in order to speed it up.  Also, I think I read somewhere that FileMaker's limit on the number of scripts it can handle on a single layout is nine.  Anything you can do with setting up a button action rather than a script, then do it that way.

           

          3) LANs will always be faster than WANs because the distance data has to travel and the number of hops means it will take longer to transfer than being right there relative to the source database host.  If using cellular signal, then the signal also plays a factor as the strength of the signal affects wireless speeds (including atmosphic signals around you).  But even with these factors considered, 7-8 minutes is a fair amount of time to wait.  To mitigate this, I recommend trying to simplify the database structure and logic.

           

          These are just points I've learned while fiddling around (and with some networking experience).  I hope this helps you out but I am sure there are others that can add some points onto this.

          1 of 1 people found this helpful
          • 2. Re: FileMaker database slow when accessed via internet/remotely

            Hi User16733,

             

            (T'would feel better If I could adress you more personally.)

             

            I suspect that you my be trying to transfer more fields in each record than is needed. This is one of FM's achillie's heels. as for any record requested ALL field are transmitted over the network. Thus if your tables contain many more field than are needed by the client, then your WAN performance will certainly suffer. We can be lazy and get away with this on a LAN but not so readily on a WAN.

             

            Mark Richman from Skeleton Key in St Louis, MO gave an excellent webinar on this very topic a couple of years ago. I've looked on their web site for an archived version but failed to find it. I'd therefore suggest you contact Mark to see if it has been archived somewhere.

             

            Marks recommendation is to have the unnecessary fields in a separate Table that has a one to one relationship with its parent. That way you only download the data that is actually needed.

             

            His recommendation may not completely solve your issue but it is worth considering.

             

            I hope this helps,

             

            John

            1 of 1 people found this helpful
            • 3. Re: FileMaker database slow when accessed via internet/remotely
              RubenVanDenBoogaard

              Hi User,

               

              I am logging in to customer databases all the time over the internet and have no such issues, speed is slower then local LAN but good workable. 

               

              The latency and speed of the internet connection is important, and then ofcoarse the structure of the database.

               

              You can easily check the latency and speed of the connection by putting a database with a single table with just data on the server and open

              it at the other end.  Watch out with (big) images in list view, they will take a long time to download, but text and numbers should be good.

               

              If that works as expected then you can look into the structure of your database, if not you have to find a better internet connection or put the

              server closer to the client. An other solution could be to use IWP or php to access the database, or put a copy locally at the store and sync

              the data back and forth.

               

              Hope this helps,

               

              Best regards,

               

              Ruben van den Boogaard

              Infomatics Software

              ruben@infomatics.nl

              1 of 1 people found this helpful
              • 4. Re: FileMaker database slow when accessed via internet/remotely
                Stephen Huston

                John's comments about WAN performance and the WAN presentation from DevCon are spot-on.

                 

                Once of the things worth noting is that viewing records in List View via WAN requires significantly more records to be cached than if only viewing in Form View.

                 

                Your experience that the first refresh is the slowest, and thereafter things speed up is a clear sign that this is a network record-caching issue in your system.

                 

                Record caching is worse (more records have to cached across the network) in List View than in Form View, and worse with wide tables (more fields) than narrow tables because FM caches all fields except undisplayed unstored calcs and undisplayed containers.

                 

                Another recommendation from a different DevCon presentation that year was that tables which significantly exceed 50 fields should be restructured -- aiming for 35 fields max and avoiding unstored calculation fields.

                1 of 1 people found this helpful
                • 5. Re: FileMaker database slow when accessed via internet/remotely
                  user16773

                  Thank you everyone. I am going to try your suggestions.

                  Hope they work!!!

                  • 6. Re: FileMaker database slow when accessed via internet/remotely
                    thirdsun

                    You need to be aware of the fact that FileMaker solutions for WAN access need heavy optimization. Things you won't think about twice in a local environment need careful consideration when deployed for remote access.

                     

                    Here are some things I always take into account for these solutions:

                     

                    • Index everything! Unindexed fields and unstored calculations are your enemies. Considering that one of your summary fields requires 7-8 minutes of loading I have no doubt that there is an unstored field involved.
                    • If you have calculations that reference related data you obviously can't store it. However that isn't an option, you'll need to work around that. Instead of a calculation you could use a regular data field that has its content changed via ScriptTrigger everytime one of the involved related fields is changed.
                    • List views will be slow to scroll if you show any data from related tables in the body part. You'll need to find ways to only show fields from the current table in list view, e.g. if necessary save that data in multiple tables - be sure to keep it in sync though ( ScriptTriggers again)
                    • The reason for those slow scrolling lists is the fact that as soon as you show one related field FM will fetch the whole related record. This is the case everywhere: If you touch it you own it.
                    • Avoid graphics. The occasional image isn't a problem, but don't go excessive: These days you can use FileMakers internal UI tools for many things that needed images before. In addition I urge you to use Icon Fonts like Entypo or FontAwesome. They are lightweight and, due to being simple text, available for conditional formatting.
                    2 of 2 people found this helpful
                    • 7. Re: FileMaker database slow when accessed via internet/remotely
                      CICT

                      Hi

                       

                      We believe you have 4 options, all of which involve compromise:

                       

                      1. Design for delivery via the web (IWP, CWP, PHP, etc), which involves significant additional development and will not provide much of FileMaker's full functionality

                       

                      2. Design the database for operation over a WAN, again significant consideration will need to be given to the design and some standard FileMaker features will need to be avoided

                       

                      3. Use a hosted service delivering via Citrix XenApp or similar, where the database can be designed for use as if it were running on a LAN and very few compromises need to be made, except that the hosting costs are higher than for the above 2. I believe some of your colleagues may be customers of ours using this service already and suggest you discuss this with them, who would be more impartial than we are. A mid way option here is to use Microsoft Remote Desktop, but this is not as transparent for the user, slower and more restrictive, but may be cheaper. The prices for these options are more expensive due to the Microsoft and Citrix licenses required.

                       

                      4. A combination of some or all of the above. Use web access for users requiring simplistic input/output, release sections of your database that have been optimised for use over a WAN for specific data access/editing or offline use using FileMaker Go and then use Citrix accounts for 'power users' who require full access to FileMaker for running reports or where the database is their primary tool for work and need full FileMaker functionality.

                       

                      At this point in time, there is no perfect solution to running FileMaker over a WAN, but there are ways around it.

                       

                      I hope this helps.

                       

                      Andy

                      • 8. Re: FileMaker database slow when accessed via internet/remotely
                        mr_scott

                        Hi, THX-16773:

                         

                        Good suggestions from all, so I'd like to add to what they've said.

                         

                        Mark Richman recently replied to an email I sent regarding the performance of FMRPC, which his company (Skeleton Key) co-developed with 360Works.

                         

                        "Any time a Pro or Go client sorts, summarizes, replaces across or performs

                        an ExecuteSQL operation on a batch of records, all of those records are

                        read down the client. When a Pro or Go client sees just one field in a

                        record, that record and the next 24 after it (in Form view, assuming the

                        found set is >= 25 records) get read as well."

                         

                        So, if this has to come over the Internet (WAN, or even worse, VPN over Wi-Fi WAN), your SubSummary parts and data are going to slow things down.

                         

                        There are a couple of more things to understand. Are you just displaying/rendering data for reporting, or, does it need to be interactive (modify data points in a record, or "Go to Related"?

                         

                        If your answer is either, you can increase the speed with increased "normalization" (break out large tables into related smaller tables, wherever possible), coupled with "Proxy Tables" and "Pointer Records" that only pull the data you need. These methods require the use of number fields in the Proxy table for summarized and calculated values from the related tables.

                         

                        Mark Richman will be conducting a regular DevCon session where he will "clarify and discuss 'does move' and 'does not move' scenarios, and the impact of each on subsequent operations".

                         

                        Best regards,

                        - - Scott

                        • 9. Re: FileMaker database slow when accessed via internet/remotely
                          wimdecorte

                          I don't think the statement about the ExecuteSQL sending all the data over is accurate.  The query is run on the server and only the result is sent back to the client.  The only time the whole record set is sent over is if the user that does the query has an open record in the table targeted by the SQL query.

                           

                          I've done a fair amount on performance testing on this and the result on very large record sets (> 1million) indicate that the server does the work on this one.

                          • 10. Re: FileMaker database slow when accessed via internet/remotely
                            mark.richman

                            Sorry I'm late to this thread.

                             

                            Wim, I agree.

                             

                            ExecuteSQL is very much like a native FileMaker find - the server does the query and sends the list of Record IDs back to the client. If the client has any of the needed records in their temp file, those records do not need to be fetched again. That said, any of the other records that met the find criteria will be fetched, no matter how many or few columns your SELECT statement requested. In this regard it's different than FileMaker's pre-fetch, which works in smaller batches.

                             

                            I  believe, also, that if the SELECT statement uses a GROUP BY, DISTINCT, ORDER BY or other aggregate function, the WHERE clause will be applied first, thus reducing the records that are fetched to just those that met the criteria, and then the aggregate actions will be performed on the client. This too is like the FileMaker native behavior - you get the list of Record IDs, and then fetch any records from that set that are needed to evaluate the aggregates or resolve the sort order, i.e. all of those that met the criteria.

                             

                            Hope that helps. Wim, if I'm wrong, pleae advise :-)

                            • 11. Re: FileMaker database slow when accessed via internet/remotely
                              davehob

                              Also late - apologies - but I got some great help with a similar post a while back.  I summarised the suggestions in a blog post here.  The original thread is here.

                               

                              Dave.

                              1 of 1 people found this helpful