1 2 Previous Next 21 Replies Latest reply on Jul 26, 2017 11:16 AM by haider2016

    ExecuteSQL or Find?

    haider2016

      Hello there,

      The following screen shot shows a table contains almost 19 thousand records, I am trying to find how many students passed in each subject. Each subject has its own number (SubNum). I have written a script and used Find inside specifying each subject then I took the result of get(found count) , it looked nearly to 30 seconds for each subject and about 2 hours for all 290 subjects. Later I tried to use ExecuteSQL function instead of Find , unfortunately it took almost same time.

      Is this normal , or it can be faster !

      Any help would be very appreciated.

      Kind regards

      Screen Shot 2017-07-23 at 14.08.06.png

        • 1. Re: ExecuteSQL or Find?
          beverly

          Yes, it is a lot of records! Did you try a normal summary field

          Total of ("pass")

          then Sort by "SubjNum"

          If you need a report, you could have a summary part (with no body part) and place the two fields ("totalPass" & "SubjNum") on the summary part. Sort by "SubjNum" and report (and export if desired!)

           

          Beverly

          • 2. Re: ExecuteSQL or Find?
            CarlSchwarz

            Are the fields that you are doing the find / SQL statement on indexed?  I don't know the specifics of your find but I imagine that it should be much faster.

            Also are you doing this to a server hosted over the internet?  If so you could use "perform script on server" to run the script and it will be much faster.

            • 3. Re: ExecuteSQL or Find?
              philmodjunk

              You can also store the counts thus produced in simple number fields of a related table. Then, it may take a while to generate these counts, but only once. Then you can pull up s report of very quickly after that.

              • 4. Re: ExecuteSQL or Find?
                fmpdude

                19 Thousand Records is nothing. You should be getting nearly instantaneous response from that.

                 

                However, having just said that (trying to avoid "foot in mouth disease"), do you have a "sanitized" (non-private) representative dataset with the SQL you used?

                 

                I will try to reproduce your findings and also import that representative data into MySQL, for comparison, which is often much faster.

                • 5. Re: ExecuteSQL or Find?
                  MichaelManousos

                  The number 1 that you have as Pass how is it calculated?

                  Do you have a table for the Subjects? If thats the case you could just have a number field in that table and everytime you have a student that passes you just have the field self+1.

                  So when you want the report you just have a list view of the subjects with that field (the totals per subcet) in a fraction of second because you dont find anything with or without sql.

                  Its a "technique" we used in the old days but sometimes it is still helpfull.

                  Lets say for example you have an invoicing database each time you issue an invoice you have a number field that adds the total. Then you have no matter how many thousand records you have the total without searching, Of course you have to look out for deletes etc. But you can have a script from time to time to calculate and correct if needed the total

                  • 6. Re: ExecuteSQL or Find?
                    FileKraft

                    19 thousand records is nothing - i agree - with some restructuring of the data and the right query maybe through a relationship also it should show up in an instance (even in FMP).

                    • 7. Re: ExecuteSQL or Find?

                      can you share the esql you are using?

                       

                      Another option is , assuming you have a subject table, is to create additional fields in that table and relate it to marks table

                       

                      SubjectTable: :SubID ---< Marks::SubjNum 

                      WHERE SubjectTable contains all 290 Subject IDS

                       

                      Then In SubjectTable have calc fields  TotalRecords & PassCount

                       

                      TotalRecords = calc   Count(Marks::SubjNum)

                      PassCount =  ValueCount(FilterValues ( List(SubjNum::pass );1))

                       

                       

                       

                      I tested this with ~50,000 records on a laptop and it took a few seconds

                      • 8. Re: ExecuteSQL or Find?
                        karina

                        Hi,

                         

                        Is the the type of the subject field or the pass field a calculation?

                        If so, in the past we had the same problems, we did the following;

                         

                        You can you copy (or if possible change) the fields and, change the type of the new field to a number or text field and auto enter them with the calculated value.

                         

                        Place the new field on a new layout and then fill the values with a find and replace. Then try to do the same find and look if it's fast.

                         

                        For find's and reporting we changed as much as possible calculated fields for Ae fields, sometimes when it was not possible or not very handy we added an Ae field for the reporting en kept the calculation field.

                        We created a server script (also possible with a robot scrip) to fill the fields at night

                         

                        If you want to try this, it's better to try it in a backup first

                         

                        Greetz,

                        Karina

                        1 of 1 people found this helpful
                        • 9. Re: ExecuteSQL or Find?
                          bigtom

                          19k is no big deal. I have run finds and eSQL on 6M records and still get results in less tha 10 seconds.

                           

                          Indexing helps.

                           

                          Searching on unstored calcs is bad.

                           

                          Running eSQL from a layout not based on you search table helps.

                          • 10. Re: ExecuteSQL or Find?
                            haider2016

                            Thanks brianb for your reply,

                            TotalRecords = calc   Count(Marks::SubjNum)

                            PassCount =  ValueCount(FilterValues ( List(SubjNum::pass );1))

                             

                            I have put two calculation fields as you mentioned, the second line is little bit confusing, since both SubjNum and Pass are fields in Marks table. Nothing appeared in both fields , they are empty .

                            • 11. Re: ExecuteSQL or Find?
                              haider2016

                              Hi beverly, and thank you for your reply. I have already used total of ("pass"), the time was spent to find all records that match subject number. 

                              • 12. Re: ExecuteSQL or Find?
                                haider2016

                                Thanks CarlSchwarz for your reply, yes, both fields are indexed.

                                • 13. Re: ExecuteSQL or Find?
                                  haider2016

                                  Thank  you Michael Manousos, good point, I will play around with that.

                                  • 14. Re: ExecuteSQL or Find?
                                    haider2016

                                    Thanks Karina for your reply, YOU are the man

                                    1 2 Previous Next