4 Replies Latest reply on Mar 31, 2014 6:30 PM by taylorsharpe

    Indexing and server performance

    ErikWegweiser

      I just wanted to verify I'm doing the right things...

       

      We have a large system that I'm trying to optimize.

      Several related files (yes, developed in v6 a long time ago, but we're keeping the separation model for a few reasons).

       

      - In our large Contacts database, I have segregated some less-populated fields into parallel tables.

      For instance, few have email addresses, so that's in a related table; rarely if ever searched.

       

      - Yes, any field that is being used for searches or keys is indexed.

       

      - Most fields used for searches are indexed as well, with the exception of some fields used

      only for sorting small sets of records in an unfiltered portal (this is FM 10!)

       

      I have turned OFF indexing on as many fields as possible when I know they will never be

      used to search or sort. For example, Notes: While there may be several notes records

      for a Contact, I don't want to store what woud be a hugely varied index of that field.

      Understood that this means searching such a field is prohbited (would at least take a long time).

       

      Just displaying the set of notes records does not depend on the field being indexed (only the

      key field for the related notes table need be indexed, of course).

       

      I want to make sure I have the correct concept. While I understand it is generally recommended to

      index for efficeincy, I also interpret this to mean only when required, as indexes increase

      size and decrease the efficiency at least somewhat.

        • 1. Re: Indexing and server performance
          taylorsharpe

          Yes, you generally understand the purpose of indexes and how they aid the performance of searching and filtering.  You also noted that having indexed fields makes the file bigger, which can be a draw back.  And some fields like Notes fields that are rarely ever searched rarely should not be indexed.  Not indexing fields used to be more of an issue when hard drive space was expensive, but it is relatively inexpensive now and I would not use that as a reason for not indexing fields. 

           

          While indexing speeds up searches, it also has a penalty for creating and deleting records in that every field that has an index has to be updated when you create or delete a record.  While individual records don't have a noticeable speed issue, if you're doing imports or deleting a large number of records, it can make FileMaker seem very slow.  Unlike in some SQL engines, when FileMaker does a delete, it seems to update the index after every delete and the same with each record imported.  This means between each record, it is updating a bunch of indexes.  This can make large deletes and imports really painful on FileMaker. 

           

          There are other reasons for indexing fields like you need them for Value Lists and relationships.  But it is also good to try and not index fields when it is not necesary. 

          • 2. Re: Indexing and server performance
            taylorsharpe

            One other performance thing to mention is that FileMaker handles a whole record at a time.  So if you enter a field, you record lock the whole record and not just that field.  For this reason, tables with lots of fields are not well optimized in FileMaker and you do better to have several tables with one-to-one relationships instead of one table with a whole lot of fields.  FileMaker then pulls and stores only the fields of the table of the field you are clicking in instead of all of the fields. 

             

            I actually have one client who has a table with 1500 fields in it.  This is just one of those things that becaues FileMaker lets you do it does not mean it is a good practice <grin>.

            • 3. Re: Indexing and server performance
              Mike_Mitchell

              One additional feature (?) that bit me some years ago. When doing bulk imports, not only do the records have to be updated, the indexes do as well. This degrades performance and can, in certain circumstances, contribute to instability in the database. I found that doing what you've proposed (turning off indexing on all but essential fields) dramatically improved the speed of the imports and reduced the instances of index corruption.

               

              Mike

              • 4. Re: Indexing and server performance
                taylorsharpe

                I once had a very large table (83 million records) and I would turn off the indexing before the imports and turn the few fields that needed index back on afterwards (there were only like 3 fields that needed indexed, so it was easy).  If someone regularly needed to do this, I wonder if there is a scripting method of turning off indexing and turning it back on.  I used MMQuery for schema modfications, but it doesn't looked like it supports turning indexing off, do the import, and indexing back on.  And I couldn't figure out how to do an Alter table in a SQL call to do it, but I bet there is a way with someone who is smarter with SQL than me.