4 Replies Latest reply on Feb 27, 2012 12:16 AM by jobemay

    Fields, Repeating Fields, Calculations? Which are best for performance?

    jobemay

      Hello,

      I'm working on a big database (special verb dictionary). Which variant will provide best performance? Is there a big difference at all?

       

      Example: Want to show "arbeiten arbeite arbeitest" in a layout. (German for: "(to) work (I) work (you) work"). Database with about 10000 records

       

      1. a single textfield for each item: F1:infinitive > (arbeiten); F2:1stPerson > (arbeite); F3:2ndPerson > (arbeitest) -> (gives 3 fields in the database, and 3 fields in the layout. DISADVANTAGE: 3 fields for each verb, even if it is a regular verb)
      2. 1 textfield(3 repetitions) for the root, and 3 for the suffixes: F1(3 repetitions):root > (arbeit); F2:suffixInfinitive > (en); F3:suffix1stPerson > (e); F4:suffix2ndPerson > (est) -> (gives 4 fields in the database, and 4 fields in the layout. ADVANTAGE: Only 1 field for each regular verb, the others are globals. DISADVANTAGE: layout building more cumbersome)
      3. 1 textfield for the root, and 3 for textcalculations: F1:root(arbeit) F2:(F1 & en) F3:(F1 & e) F4:(F1 & est) -> (gives 4 fields in the database, and 3 fields in the layout. ADVANTAGE: Only 1 field for each regular verb, the others are globals)
      4. 4 textfields (1 for the root and 3 for the suffixes) and 3 for textcalculations: F1:root > (arbeit); F2:suffixInfinitive > (en); F3:suffix1stPerson > (e); F4:suffix2ndPerson > (est); F5:(F1 & F2); F6:(F1 & F3); F7:(F1 & F4) -> (gives 7 fields in the database, and 3 fields in the layout. ADVANTAGE: Only 1 field for each regular verb, the others are globals)

       

      Does the number of fields affect overall performance?

      Do calculations affect overall performance compared to textfields?

       

      Thanks

      Jurgen

       


       

       


        • 1. Re: Fields, Repeating Fields, Calculations? Which are best for performance?
          comment

          IMHO, before you ask  which structure will provide the best performance, you should ask what is the structure that will allow you to accomplish your goal (of which you have told us nothing about).

           

          In general, whenever you have three variants of [something], the parent-child model is called for. In your case, that would probably mean a Verbs table (parent) and a Conjugations table (child), linked by VerbID.

          1 of 1 people found this helpful
          • 2. Re: Fields, Repeating Fields, Calculations? Which are best for performance?
            Malcolm

            I'm working on a big database (special verb dictionary). Which variant will provide best performance? Is there a big difference at all?

             

            You are barking up the wrong tree. You should be concentrating on designing for functionality. When you achieve full functionality you can make decisions about the performance.

             

            Example: Want to show "arbeiten     arbeite     arbeitest" in a layout. (German for: "(to) work     (I) work     (you) work"). Database with about 10 000 records

             

            That is a good number but Filemaker will barely blink.

             

            Does the number of fields affect overall performance?

             

            Only at a very much larger scale, ie, many dozens of fields. Or when you are running across a WAN/internet. If you are running on a fast local network you may never notice the affect.

             

            Do calculations affect overall performance compared to textfields?

             

            Yes, though it appears as though the calculations you are envisaging would be able to be stored. That would give them similar performance characteristics to plain text fields.

             

            Unstored calculations performed on large record sets are slow.

             

            At 10 000 records you may see a dialog displayed momentarily when searching, sorting and sub-summary reporting. .

             

            Malcolm

            • 3. Re: Fields, Repeating Fields, Calculations? Which are best for performance?
              LyndsayHowarth

              LOL... I saw the German version of this first with zero responses... silly me!

               

              Michael and Malcolm have covered the issues well... to which I will add the following points:

               

              1. I wouldn't necessary separate the verbs conjugations into another Table. They are safer in a single table and this will also allow indexing for searching to be in a single table. There are not unlimited conjugations so using repeating fields would be fine. They can always be split later very simply.

              (*This assumes that you don't want to find all records for only one tense or such... in which case you would create a single record for every variation and have self-relationships in the same table.)

               

              2. Performance on the WAN will also be effected by the graphic elements on the layouts.

               

              - Lyndsay

              1 of 1 people found this helpful
              • 4. Re: Fields, Repeating Fields, Calculations? Which are best for performance?
                jobemay

                Hello Lyndsay, Michael and Malcolm,

                 

                (is there a way to dedicate a reply to more than one person in this forum?)

                 

                Malcolm, you have covered all I wanted to know. Didn't want to say anything about functionality and goal of the database (you critised that, Michael) to keep it short. Was mainly interested in the question of performance of different concepts in general.

                 

                Michael, thanks for the parent-child concept reminder.

                 

                Lindsay, thanks for advice 1. and 2.. I'll keep them in mind.

                 

                Jurgen