11 Replies Latest reply on Oct 21, 2016 9:23 AM by restored18

    FileMaker ODBC Performance Problems

    restored18

      Has anyone ever had performance issues with a FileMaker database that is regularly accessed through FileMaker Pro clients and via ODBC on FileMaker Server?

       

      We have a couple web apps that run some select and insert queries against a database hosted on FileMaker server. Lately we've noticed a real slow-down in the database's performance while open in a FileMaker Pro client. This database will only have around 30-40 users at any given time. We have a good server setup with a quad-core Intel processor, SSDs and 16GB of RAM so I don't understand why FileMaker Server is struggling so much to handle the database.

        • 1. Re: FileMaker ODBC Performance Problems
          mikebeargie

          You haven't provided any info about your data.

           

          Chances are one of the select statements you are running on a regular basis is searching on an unindexed and/or unstored field in a large table and has to wait for that calc to evaluate on the x number of records you have.

           

          You can try switching to indexed fields only in the select statement to see if that improves performance (it should).

          • 2. Re: FileMaker ODBC Performance Problems
            restored18

            All of my queries are selecting indexed fields or stored indexed calcs. We also have the performance issue when inserting new data which would not force any unstored calcs to run.

             

            The select query at issue has an inner join with a where and order by clause, but that's pretty standard for SQL.

            • 3. Re: FileMaker ODBC Performance Problems
              mikebeargie

              But in filemaker an inner join requires filemaker to evaluate all of the related data as well to parse a result back to you, so essentially if there are any unstored calcs, summary fields, etc.. in either the parent or the join table, you’ll see a performance hit.

               

              Try this out:

               

              Make a form view in filemaker for the parent table.

              Add all of the fields for the parent table and join table onto that layout.

              Do a find similar to your select.

               

              Does it take a while to perform the find?

               

              It also matters how MUCH data you have to sift through. Usually ODBC calls to filemaker get slower over time if the table is growing by a large amount daily. EG a complex join may be instantaneous for a few hundred records, but a few thousand is a bit slower, a few hundred thousand is much slower, etc..

               

              Have you tried doing your inserts into an empty temp table and moving them in filemaker afterwards?

              • 4. Re: FileMaker ODBC Performance Problems
                restored18

                A similar find in FileMaker has significantly better performance. The data is coming out of a legacy database that has thousands of records in the main tables so that is probably the source of the slowness. And of course I do have some summary and calculation fields in both tables even though they are not being queried.

                 

                What is surprising to me is not just that the OBDC queries run slowly but that it can affect the responsiveness of all of the users accessing the database through FileMaker Pro. I guess the extra load of the queries affects the entire server.

                • 5. Re: FileMaker ODBC Performance Problems
                  philmodjunk

                  Hmmm, inserting data can be slowed by having many indexed fields as the inserts have to update the indexes as well as the table into which records are being inserted. But I normally see such issues with record counts near a million or more...

                   

                  I wonder if those indexes are healthy or need to be rebuilt?

                  • 6. Re: FileMaker ODBC Performance Problems
                    restored18

                    How often should indexes be rebuilt?

                    • 7. Re: FileMaker ODBC Performance Problems
                      philmodjunk

                      Maybe never. I've never really figured out why sometimes there's an issue. Usually, the key indicator is that finds or sorts aren't working as expected or you get a record where all the fields are question marks. In your case, isn't the field from an external SQL data source and not an actual FileMaker file or did I misunderstand?

                       

                      If so, I truly don't know as it would depend on the other Database software.

                       

                      If the table is a standard FileMaker table, you can use advanced recover options to rebuild the file to rebuild all indexes or you can turn indexing on an off in Manage database to rebuild them one at a time.

                       

                      In advanced recover options, select "copy file blocks as is" and "rebuild indexes now". To get a new file with rebuilt indexes.

                      • 8. Re: FileMaker ODBC Performance Problems
                        PowerSlave

                        If you are using external account authentication, make sure you have the external accounts listed at the bottom of the accounts list. Failure to do so will cause any ODBC or CWP request to get very slow and when there is so much authentication going on (authenticate for each query in the case of CWP/ODBC), it will cause a bottleneck on your FMS, thus affecting performance of FMP clients as well.

                        • 9. Re: FileMaker ODBC Performance Problems
                          restored18

                          philmodjunk I am pulling data out of a FileMaker database using ODBC so updating the indexes could definitely apply. I upgraded the database to fmp12 last year which would have rebuilt the indexes, I can try again now and see if it makes a difference.

                          • 10. Re: FileMaker ODBC Performance Problems
                            restored18

                            Thanks PowerSlave, that's a good idea, I'll try that out and see if it helps.

                            • 11. Re: FileMaker ODBC Performance Problems
                              restored18

                              Looks like mikebeargie had the right answer. After testing all my queries, I found one query with an inner join that would pull about 175 to 200 records at a time. It would take 900ms to 1000ms to run. When I removed the join, it dropped to 300ms. Looks like FileMaker Server struggles with receiving queries with joins, though since this is a legacy system, the tables aren't well designed so I'm sure that is part of it.

                               

                              Thanks for all the help!