9 Replies Latest reply on Mar 18, 2015 8:08 AM by siplus

    Search speed, numeric vs text field

    siplus

      Apparently, searching on a numeric key vs doing it on a text key is 4 to 5.5 times faster.

       

      Of course the real impact might be negligible, but nevertheless. Of course, I might be wrong somewhere.

       

      Please run the first 3 scripts first, then test with the fourth.

        • 1. Re: Search speed, numeric vs text field
          mardikennedy

          But only while PKUUID remains a calc field, yes?  Change to text and speeds almost the same.

          • 2. Re: Search speed, numeric vs text field
            Markus Schneider

            in theory, fields of numeric type should be handeled faster (pure integer -what is not available in FM- should be the fastest), but in FileMaker (tested with relatively small databases), differencies are more or less meaningless (as long as all fields are inexed and not of type formula).

             

            I personally use integer values as much as possible - but because it's simple to calc (i.e. toggeling a 'flag' that can be done with abs(MyFlag -1))

            • 3. Re: Search speed, numeric vs text field
              Draco

              Remember ...numeric fields indexed 400 decimal., ... in only 100 text fields.

              regards

               

              Draco

              • 4. Re: Search speed, numeric vs text field

                If you search for a number you are searching for an exact known indexable entity.

                 

                If you search for a word, the field may not be indexed and the word might not be the first one in the field. xBase suffered from its inability to find interior words without doing a long, time consuming instring search. FileMaker blew them out of the water with its every word indexed approach. 

                 

                However, searching for a word fragment or inside a word is slower.

                 

                Do we every search for all of the numbers that contain a 9? That would be slow... 

                 

                And FileMaker has limited search capabilities, for instance I could using another database search for a complex calculation or compare two fields, etc. Similar can be done in FileMaker but not using search.

                • 5. Re: Search speed, numeric vs text field
                  jbante

                  mardikennedy wrote:

                   

                  But only while PKUUID remains a calc field, yes?  Change to text and speeds almost the same.

                  No. In the provided sample file, the PKUUID field is a stored, indexed/able calculation. Nothing triggers it to re-evaluate.

                   

                  Even comparing non-calculation text fields to number fields, number fields are consistently faster to perform finds on than text fields. Whether or not the difference is practically significant depends on the application.

                  • 6. Re: Search speed, numeric vs text field
                    siplus

                    next step: evaluate GTRR based on numeric vs UUID keys.

                    • 7. Re: Search speed, numeric vs text field
                      mardikennedy

                      <next step: evaluate GTRR based on numeric vs UUID keys.>

                       

                      Yes, that would be interesting.  It was fascinating to me to see the statistical difference in the supplied test file, when I changed the indexed UUID calc field to a text field and ran tests.  The big difference changed to a very small difference.

                       

                      Also, to be fair to FileMaker, one should not underestimate its text search abilities in the real world (as opposed to theoretical ideals).  I have one particular database on our server which is set up as a part of our corporate 'data recovery strategy' rather than being a normal FMP database.  In other words, we don't actually use it; we just back up data from an existing (non FMP) business tool because we know that the tool is 'at risk'.  Currently it (our FMP backup) has 900,000 records, flat file, and includes some fields with quite a lot of text, eg 500 words.  Unindexed I think it is around 2 - 3 GB and indexed it's roughly double that size.  I can use the QuickFind field to search a random word and pull up, say, 30 matching records in about half a second.  We consider the text search to be impressively fast.

                      • 8. Re: Search speed, numeric vs text field
                        user19752

                        I don't know about internal number format, but 400 decimal need at most 200 bytes (or +1byte for the length, using BCD), and text is UTF-16 then 100 chars need 200 bytes.

                        • 9. Re: Search speed, numeric vs text field
                          siplus

                          One of the options we have, in order to speed up our solutions, is to reduce the data getting transferred from A to B via C, where A is a server, B is a client and C is LAN or WAN.

                           

                          I created two identical databases, DB1 and DB2, consisting only of a PK and a text field.

                           

                          Both databases have 823264 records. (Don't ask why, it's meaningless).

                           

                          In both databases, the Text field is empty and unindexed for all the records.

                           

                          In both databases, the PK is indexed as per definition (before creating the records with a script) and has been searched for a specific value in the PK, then a sql which forced a sort (select distinct PK) then closed.

                           

                          in DB1, the PK is defined as text, autoenter Get(UUID)

                           

                          in DB2, the PK is defined as number, autoenter serial.

                           

                          DB1 occupies 274.5 MB on my HD.

                          DB2 occupies 30.2 MB on my HD.

                           

                          I deduct a factor of 9 regarding the opening paragraph. But maybe it's just me being a PITA.