12 Replies Latest reply on Jul 18, 2011 3:29 PM by TSGal

    When have you outgrown filemaker?

    TimHidalgo

      Title

      When have you outgrown filemaker?

      Your post

      Is there a point when you have outgrown filemaker?  We do quite a bit of etl between filemaker and mysql and daily we have to deal with filemaker locking up for extended periods of time.

      Any tips, best practice articles are appreciated.

      Thanks

        • 1. Re: When have you outgrown filemaker?
          TSGal

          Tim Hidalgo:

          Thank you for your post.

          Which version of FileMaker Server are you using?  What version of the MySQL driver are you using?  Is FileMaker Server running on a Mac or Windows server?  Which version of OS?  Do you notice what was being processed at the time of "locking up for extended periods of time"?  Any other information you can provide to help replicate the problem could help narrow the focus to a probable cause (or causes).

          TSGal
          FileMaker, Inc.

          • 2. Re: When have you outgrown filemaker?
            TimHidalgo

            Filemaker Server 11 (.03), we are using the latest odbc from actual technologies, mac os x server 10.6.  The majority of our traffic is read, we have not been able to pinpoint things that are causing the slow down.  I've been able to monitor the sql queries that are being made, and but we have very large tables so there are times when a select count(*) table_name takes 763 seconds.

            I've been able to rewrite queries and test them, but right now we are unsure as to how we can modify queries that are generated by filemaker.

            Thanks

            • 3. Re: When have you outgrown filemaker?
              TSGal

              Tim Hidalgo:

              Thank you for the additional information.

              I'm assuming the select count(*) statement usually is initially quicker in responding.  Does this slowness occur over time?  Are you able to monitor the memory activity for both FileMaker Server and MySQL during these requests for data?  Does this usually occur with a large query?  A large amount of data returned?  If you quit FileMaker and restart, do queries speed up?  If you quit MySQL and restart, do queries speed up?

              At this time, it is not possible to modify the queries generated by FileMaker.  From your statement, I'm assuming you have either a specific query or a more optimized query.  If the latter, please let me know what is the FileMaker query and what is the optimized query, and I'll make sure that information gets sent to our Development and Testing departments for review.

              TSGal
              FileMaker, Inc.

              • 4. Re: When have you outgrown filemaker?
                TimHidalgo

                Restarting mysql doesnt provide much and its also not really an option since we run other systems that use the database server.  Yes when things start backing up we end up restarting filemaker and things seem to improve for a short amount of time.

                Here is an example of a filemaker generated query SELECT COUNT(*) FROM (SELECT DISTINCT column FROM table_a) TABLE_B

                Obviously this query is one...weird, and can be rewritten select count(column) FROM table_a  Other times we can achieve better for performance by using index hints, which according to your post we wont have the option of using in filemaker :(

                • 5. Re: When have you outgrown filemaker?
                  TimHidalgo

                  Here is another example of a query generated by filemaker:

                  SELECT column FROM table WHERE otherColumn LIKE '%STRING%'

                  This is a huge table we are working with, and the wildcard at the beginning of the string causes the query to ignore indexes :(

                  • 6. Re: When have you outgrown filemaker?
                    TSGal

                    Tim Hidalgo:

                    Thank you for the additional information.  I have sent your posts to our Development and Testing departments for review and possible optimizing the SQL queries in a future release.

                    TSGal
                    FileMaker, Inc.

                    • 7. Re: When have you outgrown filemaker?
                      TSGal

                      Tim Hidalgo:

                      Our Testing has said that you can achieve the desired results by using the correct Find request.

                      Find request: a
                      SQL query: ... LIKE "%a%"

                      Find request: ==a
                      SQL query: ... LIKE 'a'

                      Find request: ==a*
                      SQL query: ... LIKE 'a%'

                      For more information about Find queries, see pages 30-42 (with emphasis on page 34) of the FileMaker User's Guide at:

                      http://www.filemaker.com/support/product/docs/fmp/fmp11_users_guide.pdf

                      Let me know if you need additional clarification.

                      TSGal
                      FileMaker, Inc.

                      • 8. Re: When have you outgrown filemaker?
                        TimHidalgo

                        According to an earlier post "At this time, it is not possible to modify the queries generated by FileMaker."

                        Also please note that your first string LIKE %a% will not use an index in the sql database

                        • 9. Re: When have you outgrown filemaker?
                          TSGal

                          Tim Hidalgo:

                          Apologies for my error.  Yes, you can change the query by what you enter into FileMaker.  Yes, the first find request will not use the index of the external data source.

                          TSGal
                          FileMaker, Inc.

                          • 10. Re: When have you outgrown filemaker?
                            TSGal

                            Tim Hidalgo:

                            Our testers would like the following information from you:

                            CPU, Hard Disk size, Hard Disk availability, Memory, network settings

                            TSGal
                            FileMaker, Inc.

                            • 11. Re: When have you outgrown filemaker?
                              TimHidalgo

                              I'm assuming you want the filemaker server stats?

                              1 x 2.26GHz Quad Core Intel Xeon

                              6GB 1066Mhz DDR3

                              Gigabit Network Cards

                              Mac OS X Server 10.6.8

                              • 12. Re: When have you outgrown filemaker?
                                TSGal

                                Tim Hidalgo:

                                Thanks for the information.  I have passed the information back to Testing.  If they need more, I will let you know.

                                TSGal
                                FileMaker, Inc.