8 Replies Latest reply on Jun 20, 2017 7:32 AM by fmpdude

    SQL Hangs in Simple GROUP BY

    fmpdude

      Product and version FMP 14.0.6

      OS and version 10.12.5

      Hardware Mac, 32 GB RAM, SSD (Plenty of free space)

      Description: SQL Hangs in Simple GROUP BY

      How to replicate: Read details below.

      Workaround: None

       

      -----------------------------

       

      When trying to help another forum member on a UUID problem, I created a simple file with 1,000,000 UUIDs (searching for possible dups as was reported in that thread).

       

      I have attached the file I created to this problem report and you can see the simple SQL in the screenshots below.

       

      I first imported the data into MySQL which took less than 8 seconds to import. I then ran the query you see in the screenshot below. That query ran, as you can see, in about 1/2 a second:

       

      (Comparison) MySQL:

      -----

       

      I then imported that same data into FMP 14 Adv. Machine has 32 GB RAM with fast SSD.

       

      That import took longer, 23 seconds. (A sub-problem) It took FMP almost THREE times longer just to import the data.

       

      However, the actual serious issue is that although I tried twice (see my related issue today on FORCE-QUIT and PLIST File), the SQL in FMP never completed in FMP. After five minutes, I gave up, as all I had was as "spinning beach-ball" and force-quit the machine. FMP is doing some kind of "sort", as you can see by the dialog below even though there was no ORDER BY in the query. Not sure what the issue might be, but so many queries are so slow in FMP as to be totally unusable for SQL. Period.

       

      FileMaker:

      -----------------------------------------------------

       

      (note: even with a 10,000,000 record UUID test, MySQL still completed the test in less than 7 seconds!)

       

      (Comparison) MySQL:

       

      Please let me know...

       

      • Is this a known issue?
      • Is this an issue that will be fixed?
      • Is this considered a bug?
      • How can I track progress?
      • When will it be fixed?
      • Will there be a version fix retroactively or ONLY for the current version?

       

      Etc...


      
 

       

      Thanks,

        • 1. Re: SQL Hangs in Simple GROUP BY
          greglane

          A GROUP BY clause implicitly sorts the data, so that's why you're seeing the dialog. The GROUP BY clause is taking about a minute. It's the HAVING clause with an aggregate function that is making your query seem to hang. The ExecuteSQL function has never been fast, but a having clause with an aggregate on a million groups is asking a lot.

           

          I know it seems trivial with MySQL, but that's really an apples-to-oranges comparison. FileMaker and MySQL each do countless things the other can't. If FileMaker was going to put resources toward improving ExecuteSQL performance, this scenario would be at the bottom of my list.

           

          I'm sure there's more to the story, but it seems like you could accomplish nearly the same result by doing a simple find for duplicate records. A quick test on your data set shows that takes about 4 seconds.

          1 of 1 people found this helpful
          • 2. Re: SQL Hangs in Simple GROUP BY
            fmpdude

            Thanks, but that doesn't fix the SQL issue. I appreciate your note, gut if you (re-)read the title of my posting, you'll see that it's about SQL in FMP, not that FMP doesn't have some internal, proprietary, way of doing the same thing.

             

            The comparison to MySQL is just for, well, comparison, to a free database.

             

            To wit, SQL is not usable for many (most with much data) operations in FMP.

             

            That abysmal SQL performance is a bug or a maybe SQL is just a toy feature that nobody at FMI took very seriously. Doesn't really matter I guess.

             

            We prefer industry standard techniques like SQL rather than proprietary FMP-only techniques.

             

            After all the frustration and lack of any SQL progress after five releases, we're ditching FMP completely in the next year or so, but in the meantime, we're changing as much FMP code to SQL as possible. And, in many cases, FMP SQL is failing miserably.

             

            Of course, then there is the related design flaw in FMP where after an FMP SQL Fails and hangs your machine you have to FORCE-QUIT FMP. Then, you've lost all your watch expressions since FMI, for some unknown reason, stored the watch expressions in a volatile PLIST file. So beyond not getting any SQL results, you're now scrambling for a backup to restore your plist file and your watch expressions!

             

            Another comparison: LIKE queries on relatively small data sets (one table, one field, too) that take 8 seconds in FMP take 0.15 second in MySQL.

             

            (Don't even get me started on FMP's (DOS batch file speed) script performance.)

             

            Thanks,

            • 3. Re: SQL Hangs in Simple GROUP BY
              greglane

              Life is too short to work with tools you don't find intuitive and powerful. I sincerely hope you find a set of tools that makes you happy.

              • 4. Re: SQL Hangs in Simple GROUP BY
                fmpdude

                Totally agree. Thanks Greg. I appreciate your replies.

                 

                Leaving FMP after 7 years was a tough choice given what I do like about it, but too much frustration about too few developer tools, poor performance, and the basic distribution model made the decision for me.

                 

                However, you're getting away from the entire point of my posting again. It's not about workarounds and it's not about me leaving FMP. Rather, if you (re-re-)read my posting's title, it's about bugs and SQL that just doesn't work in any production sense (or for me, at all).

                 

                Thanks!

                 

                 

                • 5. Re: SQL Hangs in Simple GROUP BY
                  TSGal

                  fmpdude:

                   

                  Our Development and Testing departments are aware of GROUP BY clause can be extremely slow.  FileMaker Pro is not a SQL Database and should not be treated as such.  Regardless, I have attached your post to the original report.  When more information becomes available, I will post again.

                   

                  TSGal

                  FileMaker, Inc.

                  • 6. Re: SQL Hangs in Simple GROUP BY
                    fmpdude

                    I'm confused by what you mean that FMP is not a "SQL Database".

                     

                    FMP _has_ SQL. FMP _has_ JDBC (which includes SELECT, INSERT, DELETE, and UPDATE).

                     

                    FMP _has_ third-party plug-ins that implement SQL.

                     

                    Additionally, I've written (Java/JDBC) sync utilities using tons of SQL to move data back and forth between FMP and MySQL and between FMP and Oracle.

                     

                    FMP's JDBC driver also supports DBMetaData classes so you can dynamically determine field types and such. In my view, FMP's SQL support (but not performance) is good.

                     

                    So, what exactly makes a product "SQL?"

                     

                    ---

                     

                    So, this abysmal performance (hung machine on basic queries) is "as designed".

                     

                    (Similar abysmal performance with LIKE, BETWEEN, and other)

                     

                    Got it.

                    • 7. Re: SQL Hangs in Simple GROUP BY
                      TSGal

                      fmpdude:

                       

                      There is no comparison with Oracle and MySQL (specifically designed for SQL) to FileMaker Pro.  When FileMaker Pro SQL becomes more optimized, I will post again.

                       

                      TSGal

                      FileMaker, Inc.

                      2 of 2 people found this helpful
                      • 8. Re: SQL Hangs in Simple GROUP BY
                        fmpdude

                        I didn't see any such disclaimers in the FMP SQL documentation or record limits that various SQL commands can handle.

                         

                        Consider this issue closed as you've totally addressed my concerns.

                         

                        As of this week, I'm no longer using FMP for production work.

                         

                        Thanks.