11 Replies Latest reply on Feb 28, 2014 10:30 AM by NancyAdelman

    Slow searching with ESS

    NancyAdelman

      I have a FM 13 database with an ESS table, hosted by FM Server 13. The ESS table has a foreign key that is related to a FileMaker table primary key.

       

      There are records in the ESS table that do not have a corresponding record in the FileMaker table. I want to find those records and delete them. I put the primary key from the FM related table on the ESS layout. The problem is that there are about 150,000 records in the ESS table and in the FM table and the find was too slow. I cancelled it after it processed for almost an hour. I have no idea how long it might have taken or if it ever would have finisihed..

       

      Are there any suggestions for doing this?

       

      Thank you.

       

      Nancy

      Adelman Databases

      nancy@adelmandatabases.com

        • 1. Re: Slow searching with ESS
          Mike_Mitchell

          If you have privileges to delete records, you might try using the Execute SQL script step (NOT the calculation function ExecuteSQL) and the SQL DELETE command (here's one page for how to use it):

           

          http://www.w3schools.com/sql/sql_delete.asp

           

          Might be much faster than trying to do a Find.

           

          HTH

           

          Mike

          • 2. Re: Slow searching with ESS
            LSNOVER

            I would concur with Mike.

             

            FM is not going to handle more than a couple thousand records from an ESS table in the Found Set without seriously bogging down. 

             

            In addition, once you dump the records from the SQL database using a direct call, Filemaker is going to go haywire trying to reconcile what has happened with the records in the current table.  So, if you go this route, do one of two things, don't go to a layout showing records from the table until AFTER you've done the delete.  Or, even better, disconnect and reconnect to the SQL data source via a script procedure.   This will clear Filemakers record cache and reset things, which will save Filemaker many gyrations trying to reconcile what just happened in the table behind it's back.

             

            Cheers!

            Lee

            • 3. Re: Slow searching with ESS
              user19752

              What is your ESS server ?

              Making a table in it and import all fm records, then set the constraint to fk cause all invalid records

              deleted automatically.

              But I don't know how long it takes.

              If 150,000 records "in  the FM table",  sending all keys to ESS server takes a while anyway.

              • 4. Re: Slow searching with ESS
                LSNOVER

                Yes, I'm assuming you can easily quantify the "where" clause in a SQL Delete statement and push that back to the server.

                 

                If you are allowing the user to do various Find and Omit operations to get to a specific found set, you will have to push the record IDs to the SQL server.  Even with the overhead of doing that, the operation in total will be much faster if you handle the deletes on the SQL server instead of in the context of Filemaker. 

                • 5. Re: Slow searching with ESS
                  Mike_Mitchell

                  The OP described removing the SQL records that are missing a FK to the FileMaker table. Should be simple enough.

                  • 6. Re: Slow searching with ESS
                    user19752

                    FM Execute SQL script step is limited as

                    SQL statements are limited to a maximum length of 256 K characters (512 KB).

                    So SQL like as

                    DELETE FROM esstable WHERE fk NOT IN ( list all fm pk )

                    should be over it if there are 150,000 pks.

                    • 7. Re: Slow searching with ESS
                      Mike_Mitchell

                      How about

                       

                      DELETE FROM esstable WHERE fk IS NULL

                       

                      The OP was to delete ESS records where there was no FileMaker foreign key, right?

                      • 8. Re: Slow searching with ESS
                        user19752

                        I didn't think so since "put the primary key from the FM related table on the ESS layout." is not need if so.

                        I read that mean finding on FM related table::primary key with == (empty field)

                        something like

                        SELECT * FROM esstable WHERE esstable.fk=fmtable.pk AND fmtable.pk IS NULL

                        (this is not executable SQL)

                        • 9. Re: Slow searching with ESS
                          Mike_Mitchell

                          Hmn. Well, if that's the case (where the fk is populated but there's no matching record in the FM table), then you're right, it gets more complicated.

                           

                          We might need more information to recommend anything more specific.

                          • 10. Re: Slow searching with ESS
                            LSNOVER

                            user19752:

                             

                            Yes, there's a limit to how much you can send through the SQL Script with each call.

                             

                            I break my PKs up into chunks, and then loop calling a stored procedure to update a utility table with the data.

                             

                            Then I run another stored proc to execute the actual table update/delete using a reference to the record. When dealing with large sets, it may be better to store the PKs in their own records in a utility table, or you may want to marke the actual records with a Flag field and then run the delete against the flag.

                             

                            It's certainly less straightforward than it should be, but it's doable.

                             

                            In FM13 with FM13 SERVER, I am now exporting a text field with the values and then importing them via a stored procedure.  This is much easier to do now that we have the option to Execute Scripts on the Server.

                            • 11. Re: Slow searching with ESS
                              NancyAdelman

                              Thanks, everyone for the hlep

                               

                              In the end, I exported the FileMaker records to Excel put that file on the SQL server. Then I wrote a SQL statement to delete records in the SQL table with no match in the Excel spreadsheet. Since this was a one time need, it worked fine.

                               

                              Nancy Adelman

                              Adelman Databases