1 2 Previous Next 15 Replies Latest reply on Dec 24, 2014 9:02 AM by lijnbach

    Database speed

    lijnbach

      Title

      Database speed

      Post

      Hello everybody,

      Are there general recommendations to speed up a database? I have a database with about 300 records and 600 related records. (Which is not much). Even in "List View", with just a few fields, the database is rather slow. I know that unstored calculated fields slows everything down. So, I limited the unstored calculated fields. (4 in total). I almost never use "Refresh window" because this also slows down the database when cycling threw records. All tables have a unique ID, full indexed.

      Thanks in advance.

      Hans Lijnbach.

        • 1. Re: Database speed
          philmodjunk

          Is this a hosted database?

          Hosted by what? Where? (local area network or wide area network?)

          Conditional formatting?

          Summary fields?

          How is the "slow" layout designed?

          • 2. Re: Database speed
            lijnbach

            O.K. Phil,

            Thanks for your response. It is a stand alone database (It is a runtime, running on a sever, but used only by one person at the time. But also stand alone, on my system it is very slow). And it is slow on all LayOuts with this table, no matter if it is a ListView or another one.

            But, reading Your response, I think it has something to do with conditional formatting. They are quiet extensive, because the school works with colors for each subject. And I return the colors in the LayOuts.

            At the other hand, in ListView, the colors are already on the screen, and even then cycling threw the records is slow. But I can give it a try and remove the conditional formatting for the fields. Just to see what happens. I will let you know the result.

            Hans Lijnbach

             

            • 3. Re: Database speed
              philmodjunk

              The thing about conditional formatting is that their expressions have to continually re-evaluate in order to update promptly so FileMaker has to contiually "poll" the referenced values for each and every instance of a conditional format expression (at least those currently visible) on your layout.

              And make sure that your version of FileMaker is up to date. Early releases of FileMaker 12 were notoriously slow if you had conditional formats on your layout.

              • 4. Re: Database speed
                SamRembert

                I have several databases with many hundreds of records each with some 25 fields, some related fields/records, some conditional formatting, and haven't really noticed any big slowdowns.  I will be interested in hearing if the heavy conditional formatting is the culprit.

                • 5. Re: Database speed
                  lijnbach

                  Thanks for the response.

                  I am working on it right now, and let you all know the result. My problem is not the number of fields with a conditional format, but the number of conditions. I have only two fields with conditional formatting, but both with 32 conditions.

                  I am working with the latest version Phil. 

                  Hans Lijnbach

                  • 6. Re: Database speed
                    philmodjunk

                    Sounds like you need to consider an alternative to conditional formatting. You are welcome to describe the reason for your 32 different format expressions as there could be an alternative that doesn't "load" the interface down with so many repeated "polls" of your data.

                    • 7. Re: Database speed
                      lijnbach

                      O.K. Phil,

                      Thanks. I will work it out. Because it is a "specific terminology" I need a little time to translate and describe it properly.

                      I will be back with a description tomorrow.

                      Hans Lijnbach.

                       

                      • 8. Re: Database speed
                        lijnbach

                        O.K. Phil,

                        This is the description of the reason for the conditional formatting;

                        I build a “Orthotheek” for ground schools. I used the most extensive dictionary and the internet to find a translation. Believe it or not, but there is no translation as far as I know.  (And only a few people in the Netherlands know this word). A “Orthotheek” is a kind of library for “learning materials”.

                        The Orthotheek is already in use and working fine. I just had a few minor updates and a “performance issue” when cycling threw records.

                        But there is a difference with a normal library. From the learning material the teachers and students wants to know the following things:

                        What is the subject. (Mathematics, geography, biology, Dutch, English etc.) 

                        For which grade is it. (1st, 2nd until 9th)

                        What kind of material it. (Book, atlas, computer program, internet etc)

                        Where is it. (classroom 1st grade, 2nd grade, auditorium, internetsite etc.)

                         

                        There are about 300 learning materials.

                        But, there are also 600 “supporting learning materials” (As a related table). The supporting materials have exactly the same structure, except the “subject” and the “grade”. The “subject” for the “supporting learning materials and the “grade” are always the same. These two I use to join the related table.

                        Where the conditional formatting is about is the “subject”. The whole school is used to use “colors”.

                        Geography is read, Dutch is light blue, biology is green etc. (With a maximum of 32 colors)

                        The “database manager” is able (when maintaining “subjects”) to pick the right color from a color list I build my self. (All works fine).

                        The problem is that the colors return in the LayOuts, as a conditional format.

                        (See examples)

                        Thanks in advance for your effort and time Phil.

                        Hans Lijnbach

                        PS. “Orthotheek” is from “Old Greek”. [ Ortho- = according the rules - right place. “theek is from “théke” = box, cabinet. ]

                        • 9. Re: Database speed
                          lijnbach

                          An extra example

                          • 10. Re: Database speed
                            lijnbach
                            /files/a109b61817/Example_3.jpg 2454x874
                            • 11. Re: Database speed
                              philmodjunk

                              You might experiment with the following non-conditional formatting methods to see how they compare in terms of performance:

                              You can use a TextColor function to return text of a specified color so a table of color values can be used to supply RGB values via a lookup, you can establish a table of colors and yet this textcolor function can return text of a specific color in a stored calculation field that need not re-evaluate unless you assign a different subject to that item.

                              You can use transparent fields and put a container field behind it with the same dimensions. This field can also use a relationship to look up a colored rectangle from the same look up table of colors. You can specify "expand to fit" formatting without preserving proportions and use the appearance tab to set padding to zero so that it fills the container edge to edge to produce the same effect as a color filled field. (You can by the way, create colored rectangles while in layout mode, copy the rectangle to the clipboard and then return to browse mode and paste the rectangle into a container field.)

                              (This one method used to conditionally format layouts before we had conditional formatting as an option.)

                              • 12. Re: Database speed
                                lijnbach

                                Thanks Phil,

                                I will experiment with both options. The first one seems logical, because i already have a table with the colors. I only have to add the RGB code for background en the RGB code for the text. I will let you know the result.

                                PS. I rebuilt my LayOut in the other application. (The one that crashed). System still crashes, as well in OSX as Windows. Also still didn't hear from FM.

                                Hans Lijnbach

                                 

                                • 13. Re: Database speed
                                  philmodjunk

                                  I wasn't making an either/or suggestion, the container fields would be needed if you want to see a fill color for your fields without using conditional formatting.

                                  PS. I rebuilt my LayOut in the other application. (The one that crashed). System still crashes, as well in OSX as Windows. Also still didn't hear from FM.

                                  I remember that but dimly. I suggest post your own response back to the issue report in order to pop it back up to the top of Recent Items as a way to bring it back to the attention of others.

                                  • 14. Re: Database speed
                                    lijnbach

                                    Thanks Phil,

                                    I see what you mean now, you split up the text and background. The text issue is minor, depending of the color it is always in black,  white or yellow, just for the contrast.

                                    I will make another new post regarding the crash issue.

                                    Thanks again,

                                    Hans Lijnbach

                                     

                                    1 2 Previous Next