1 2 Previous Next 16 Replies Latest reply on Feb 5, 2013 9:23 AM by astelix

    Bug in ExecuteSQL?

    astelix

      I have a table with 50k Records. Each record consists of the fields "TextID", "WordPos" and "Word".

      Field "Word" contains one word for each record and is indexed.

      I want a count for each disttinct word with a SQL GOUP BY Clause.

       

      ExecuteSQL("SELECT Word FROM txt_table"; "|"; ""¶") returns immediately with the expected result.

      ExecuteSQL("SELECT DISTINCT Word FROM txt_table"; "|"; ""¶") returns immediately with the expected result.

      ExecuteSQL("SELECT Word FROM txt_table ORDER BY Word"; "|"; ""¶") returns immediately with the expected result.

       

      ExecuteSQL("SELECT Word, COUNT(*) FROM txt_table GROUP BY Word"; "|"; ""¶") freezes filemaker. CPU-Usage goes permanently to 100%.

       

      Why that?

        • 1. Re: Bug in ExecuteSQL?
          beverly

          Don't use "*" in the COUNT() function. It's counting with all fields/columns. Use the field again:

           

          SELECT Word, COUNT(Word) AS countWord

          ...

           

          GROUP BY Word

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          • 2. Re: Bug in ExecuteSQL?
            astelix

            Thank you for your answer, but that is not the problem. It doesn't work with count(word) and it doesn't work without count() at all. It frezzes as soon as I use the group by clause.

            • 3. Re: Bug in ExecuteSQL?
              gdurniak

              But there is a difference

               

              The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column

               

              The COUNT(*) function returns the number of records in a table

               

              greg

              • 4. Re: Bug in ExecuteSQL?
                astelix

                ExecuteSQL("SELECT Word, COUNT(Word) FROM txt_table GROUP BY Word"; "|"; ""¶") freezes.

                ExecuteSQL("SELECT Word, COUNT(1) FROM txt_table GROUP BY Word"; "|"; ""¶") freezes.

                ExecuteSQL("SELECT Word, SUM(1) FROM txt_table GROUP BY Word"; "|"; ""¶") freezes.

                 

                The Count(*) Version should also work, because it returns the count of al values in a group (including NULLs).

                 

                In MS-Access  each of these runs  in milliseconds (including the (*)  Version)  (verified).

                • 5. Re: Bug in ExecuteSQL?
                  taylorsharpe

                  I have a contacts database with about 1600 names.  I ran this:

                   

                  ExecuteSQL ( "Select City, Count(*) from TRAdatabase group by City" ; "|" ; "¶" )

                   

                  It returns just as expected with no unusual search time or freezing. 

                   

                  Is it possible "Word" is a SQL reserved word that is causing this problem? If you rename the field Word, does it work?

                  • 6. Re: Bug in ExecuteSQL?
                    astelix

                    With 2000 Words it  works in seconds, with 10000 words im < 5 minutes, with 20000 words it freezes up (Access does it in milliseconds).

                    • 7. Re: Bug in ExecuteSQL?
                      astelix

                      If I use "SELECT DISTINCT Word" I get the Wordllist (in seconds to minutes). When I process this list and do a separate Perform Find/Get (FoundCount ) for each listentry in a loop  I have my result in minutes. But this solution is ugly and much more complicated than clean SQL.

                      • 8. Re: Bug in ExecuteSQL?
                        taylorsharpe

                        I created a database with the same table and field name you have above, with just over 50,000 words, and executed the following in the data viewer:

                         

                        ExecuteSQL("SELECT Word, COUNT(*) FROM txt_table GROUP BY Word"; "|"; "¶")

                         

                        It took a long time to run, almost a minute.  But it ran here. 

                        • 9. Re: Bug in ExecuteSQL?
                          astelix

                          The fastest way in Filemaker seems to be to export the records in the long wordlist and to aggregate them in the export dialog. The grouped records can be savrd as a xml. The xml can be reimported into a table. It works but it is still more complicated than working SQL. But on the other side with SQL you have to parse the results to store them permanently in a table. Here the import procedure is simpler.

                          • 10. Re: Bug in ExecuteSQL?
                            nsabel

                            The speed difference issue is not with the number of words, but the number of groups. If you have 20000 words and only 5 groups then the result would return instantly. Have you tried a sub summary set-up?

                             

                            Filemaker does NOT compare to traditional SQL databases ( NOT MS Access that is junk) and you shouldn't expect the same performance.

                             

                            Nick

                            • 11. Re: Bug in ExecuteSQL?
                              taylorsharpe

                              Out of curiosity from Beverly's comment, I tried and timed this:

                               

                              ExecuteSQL("SELECT Word, COUNT(Word) FROM txt_table GROUP BY Word"; "|"; "¶")

                               

                              instead of

                               

                              ExecuteSQL("SELECT Word, COUNT(*) FROM txt_table GROUP BY Word"; "|"; "¶")

                               

                              and it took 1 minute and 2 seconds to run.  So using "*" or "Word" doesn't make a difference. 

                               

                              Obviously FileMaker is not a SQL engine and won't compete with the speed.  However, it does give the results you expect from a SQL call. 

                              • 12. Re: Bug in ExecuteSQL?
                                astelix

                                Here I can't get it to work. The group count is about 7000.

                                 

                                To nsabel:

                                 

                                After using it for 20 years I would not say MS-Access is junk. If you know how to handle it it is in many aspects way better the FMP. Each one has its merits. But for flexible data-analysis MS-Access (together with dynamically linked Excel-Pivot tables) leaves FM far behind.

                                • 13. Re: Bug in ExecuteSQL?
                                  nsabel

                                  My appoligies I was not trying to say MS Access has no purpose. Personally I don't like Filemaker either, but thats not important.

                                   

                                  I ran some test comparing SQL to a sub summary and found the SQL took minutes to return the same data that a sub summary can return in less than a second. I understand its not exactly what your looking for, but it's probably the fastest option in FM

                                   

                                  Nick

                                  • 14. Re: Bug in ExecuteSQL?
                                    mbraendle

                                    You should try your ExecuteSQL statement with my database which has 306 million words (stop words already excluded).

                                     

                                    Probably you would need 128 GB of RAM to hold everything in memory. And at the end the statement would fail because the result is too large to be stored in a variable.

                                     

                                    Even with the XML procedure you described, you have to do the grouping task in batches and do the XSLT outside FileMaker, because the XML gets too large to be handled within the inbuilt XSLT processor.

                                     

                                    Just a question of curiosity: Is the word field indexed in your case? Do you see any difference if it is indexed or not?

                                    1 2 Previous Next