7 Replies Latest reply on Nov 1, 2010 4:46 PM by philmodjunk

    why Lookup field very slow

    KenS

      Title

      why Lookup field very slow

      Your post

      Can anyone tell me why using a lookup field in a join table would lead to many second delays when setting the key field?

      DETAILS: I have a join table related to several child tables ( a session is related a doctor, patient, procedure, and note). I have a New Session script which fills in the key/id fields of the new session record. When the NoteID_fk field is set, Filemaker "pauses" for 5-7 seconds (measured by logging current time before and after SetField script step). I've tracked the delay down to the existence of a lookup field in the session table that copies the session date from the note table. I do this because the session date is used (in other) relationships, finds, and portal filters. With everything else the same, if I check/uncheck the field's  "lookup" and re-run the script the difference in running the script is 5-7 seconds. Things I've thought to check myself: 1) NoteID field is indexed fully. 2) there are two lookup fields (the other is a to a field that is never the object of a find, relationship, etc)  and the delay is the same if either or both "lookup" is checked. 3) If I uncheck both lookups, there is no delay even with field based calcs into the same note table. 

      Any help would be greatly appreciated, as the delays in running this script multiple times get's really annoying to users.

        • 1. Re: why Lookup field very slow
          philmodjunk

          You might try re-indexing the field. Open up the field's definition, turn off indexing, exit Manage Database, re-open manage database and turn it back on.

          • 2. Re: why Lookup field very slow
            KenS

            Thanks Phil for your reply, but try as I might, your suggestion and anything else I've tried does not affect this delay. 

            Added information:

            Two file soln, UI file and data file. Data file is on server connected by FMNET. Script in UI file creates new records in tables in the data file and sets the key field. Re-indexing didnt help. If I uncheck lookup, even with other calc field that reference into the joined record there is no delay. Only when I add the lookup field do I get this delay.

            Alternately, if anyone can suggest an alternative way to structure this. Many sessions can refer to a note. Sessions are at times sorted and found by date, which is kept in the note record. I use the lookup to copy the note::date into the session record for relationships, sorting, and finding.

            Any help is greatly appreciated.

            • 3. Re: why Lookup field very slow
              philmodjunk

              "Script in UI file creates new records in tables in the data file and sets the key field."

              Sets the foreign key or the primary key? If the primary key, that seems a dangerous way to set things up. If the foreign key, how does your script do this? This can often be automated as a side affect of creating a new related record, by entering data in a blank portal row, for example.

              Our system uses looked up value fields to copy current pricing into invoice line items--a join table between Invoices and Materials--thousands of times a day over our LAN with no noticeable delays, so I'm looking for anything that might be different here.

              • 4. Re: why Lookup field very slow
                KenS

                Script in UI file **pseudocode**

                Goto Layout Note

                New Record

                Set Variable $NoteID = Note::NoteID_prime

                Goto Layout Session

                New Record

                Set Field Session::NoteID_fk = $NoteID    <---- this is the script step that takes many seconds

                Session and Note are defined and related by Session::NoteID_fk = Note::NoteID_prime in the data file. Session table has fields that reference Note, some are calc's based on the length of the note's text fields. One session field is a lookup into the Note::Date based on Session::NoteID_fk. Does this help at all?

                • 5. Re: why Lookup field very slow
                  philmodjunk

                  Sounds like the delays may be due to what happens immediately after that step, "some are calc's based on the length of the note's text fields. One session field is a lookup into the Note::Date based on Session::NoteID_fk."

                  You might try selectively disabling each of those features to see what impact, if any each has on this delay.

                  • 6. Re: why Lookup field very slow
                    KenS

                    I had the same thought, so I

                    commented out all the calc fields -- no change in delay

                    unchecked the lookup -- delay gone

                    put calc fields back -- delay still gone

                    re-check lookup -- delay is back

                    reindex the field as you suggested -- no change in delay.

                    What happens internally in Filemaker with that lookup in place? What kind of things might make the for the delay? Does Filemaker transfer any records back/forth from server/client that could account for it?

                    • 7. Re: why Lookup field very slow
                      philmodjunk

                      I don't get a delay under the same circumstances you describe unless something more than a look up is involved. How much data is being looked up? Does it need to be looked up?

                      A lookup physically copies data from one table to another, so if there is a lot of data being looked up (and you might have more than one field where data is being looked up when Session::NoteID_fk recieves a value in your set Field step.)