3 Replies Latest reply on Apr 19, 2011 4:32 PM by LaRetta_1

    Certain Find Criteria Slows down find



      Certain Find Criteria Slows down find


      We have an "item" field that may contain part numbers (ex: CTC2410-R or CES-3R-3716). The "item" field is indexed and there are about 250,000 records in this database.

      If we search on the "item" field using only characters, the results return almost instantaneously! Ex: CTC2410

      However, if we search using a dash (or hyphen), it slows down the find tremendously, sometimes almost 30 seconds to return the results. Ex: CTC2410-R

      Am I missing something or is there something I do not know about that would be causing this search to bog down?

      Thanks in advance.

        • 1. Re: Certain Find Criteria Slows down find

          Searching for the first part of a word, such as CTC or CES would be faster than searching for character further in the word.

          Is the indexing (on the text field) set to 'minimum' or 'all'?

          Under Storage Options also, is the language set to English, Default, Unicode?

          Is this data imported?  I ask because some translations of the dash can bring in non-standard characters.

          • 2. Re: Certain Find Criteria Slows down find


            Indexing is set to ALL and Language is set to English.  Data is not imported; mainly entered or looked up from a Components database (FMP database as well).

            If I search the "Item" field and only specify "R", I mean I specify only one character, it returns very, very quickly.

            If I search the same "Item" field and specify "-R", it takes 30-40 seconds, just by adding a hyphen in front of the R.

            Same is true no matter where I use the hyphen in the word.  Wasn't sure if the hyphen is  a "special character"?

            By the way, thanks for your responses!

            • 3. Re: Certain Find Criteria Slows down find

              I am not sure why you getting these results but I have a hunch.  Please try something in this sequence  (I am not where I can test) ...

              Create script with:

              Set Variable [ $$start ; Get ( CurrentTimeStamp ]
              Perform Find [ Restore ... and select this text field and type R and Add
              Set Custom Dialog [ Get ( CurrentTimeStamp ) - $$start ]

              Then run 4 tests and exit and restart FM before each of these tests.  After each test, write down the number of seconds the test took (which will display in the custom dialog).  After each test, change the indexing and restart FM and after the first two tests, be sure to change the value in the Perform Find to -R instead of just R.  Resulting list should look like: 

              Indexing ALL R ## seconds
              Indexing Min R ## seconds
              Indexing ALL -R ## seconds
              Indexing Min -R ## seconds

              I believe it has to do with word vs value indexing.  The dash is a word separator in each of the two example strings you presented which means that your index has to search two words for every string (because it is on ALL, the size of your index has doubled).  This is just a guess which is why I'm asking for the tests.