6 Replies Latest reply on Jun 26, 2013 5:38 AM by mikebeargie

    Concern Regarding Database Size...


      Hi Everybody...


      I recently updated a Filemaker project management solution that's been in use for 5 years. I upgraded it to Filemaker version 12 from 11, and created a brand new graphic user interface for it; totally overhauled the look and feel.


      The new GUI uses a good amount of imported 72 dpi Adobe Illustrator objects, and the disparity between the size of the old database vs. the size of the new version has raised questions in my mind about the prospective performance of the new version.


      The database contains around 300 different fields spread across 12 different tables. There are 103 layouts, though of course not every layout is visible to the user, imported graphics don't appear on every layout, and many fields appear on no layouts.


      The old version of the database, containing 5 years of data related to over 200 projects, is only 20 megs in size. The new database contains only dummy data for one project, and it's a little over 58 megs.


      I have three concerns regarding the size of the new version of the database: 1) the impact of the graphics, 2) the impact of the text data to be entered into the database; and 3) the impact of the pdfs that will be linked to some of the records.



      As mentioned, the GUI is largely comprised of 72 dpi Adobe Illustrator objects. It's a beautiful, creamy-looking monochromatic interface that's easy to understand and very pleasing to the eyes of all the users who've seen it. It's larger than the old GUI as well; I increased the height and width of the layouts so as to be able to take advantage of today's larger monitors. These benefits obviously come at a cost; the new database is logically heavier. The layouts don't refresh as quickly as the old solution, and the portals don't scroll up and down as nimbly; I noticed that right away. Having said that, I'm happy with the speed at present. Funnily enough the old version of the database has a GUI largely comprised of imported 72 dpi Adobe Photoshop jpegs, but these are lighter than the Adobe Illustrator objects; this may or may not be attributable to the overall increase in the height and width of the new GUI.


      Text Data:

      More than the graphics, I'm concerned about the extent to which the inclusion of thousands of text records over the coming years will impact the size and speed of the database. While the database includes 2 container fields (described below) the database primarily stores only text, though it must be said that in many cases the text will be copied by the users from Microsoft Word and/or Excel files and pasted into the various fields (thereafter using the Command-Z function to normalize the style of the pasted text). What has me most concerned is that the inclusion of dummy text data for a single partially-developed project (comprised of about 300 records across 5 to 6 tables, including subtables) increased the size of the new database from 57.4 megs to 58.1 megs. The client will add no less than 3 fully-developed projects per year (let's call it 2 megs per fully-developed project), along with dozens of partially-developed projects which will each be comprised of only a fraction of the data of a fully-developed project. Assuming the two meg per fully-developed project estimate is accurate, let's guesstimate an increase of 10 megs per year altogether. Is it correct that text data takes up this much space?


      PDFs in the Container Fields:

      As mentioned, there are only 2 container fields. Each container field resides in a separate table; let's call them Table A and Table B. A single fully-developed project will contain no less than 200 records in Table A, and each record in Table A will have a single corresponding pdf that will be between 500k and 6 megs in size; Table B in a single record will contain no less than 100 records, each with a corresponding pdf that also will be between 500k and 6 megs in size. The database takes advantage of FM 12's new container fields; the pdfs aren't embedded in the database itself, and use FM 12's external secured storage. The increase in size described above is particularly concerning to me because it's attributable solely to the inclusion of dummy text records; I didn't attach dummy pdfs to their corresponding dummy records, and yet the database grew by almost a meg.


      Given the above considerations, at what point is this thing going to become unwieldly in terms of performance? Which of the above three considerations is likely to have the most impact?


      I'm at this point considering moving certain tables into separated related databases for scalability, and having the users operate from a "main menu" database that will feature links to the related databases. If the company ever grows to be a gigantic operation with multiple departments, sequestering the data in separate databases may actually be required in order to safeguard data and overall operations across several departments; but that's not something that's going to happen within the next 10 to 12 years, if ever. In the meantime, I'd like to avoid having the users suffer to move windows around or stack them on top of one other, like the old Filemaker days.


      Any of you folks have any experience to share? I'd be very grateful...



        • 1. Re: Concern Regarding Database Size...

          Don't worry!


          I'd call this a small size database. The numbers you gave don't say much.

          To get a closer picture you should make a Compacted Copy AND a Clone with each version.

          Further it is important that in both versions the same fields are indexed.


          Please come up with the sizes of these files and the platform you did the conversion on.

          Finally make a test-recover on the old FM11 version to see wether it reports any errors.


          The size of text fields have little to no impact on the "speed". You should elaborate what exactly you mean by speed.





          Check out FMDiff to compare two FileMaker files and test for file corruption at <http://www.fmdiff.com>

          Linkedin http://de.linkedin.com/pub/winfried-huslik/2/505/1a1/

          • 2. Re: Concern Regarding Database Size...

            Thanks, Winfried; your comments are assuring, thanks much!  Both the old and new databases were developed on a Mac for a mixed network.  No errors in the test-recover.  By speed I mean things like the time it takes to redraw a layout when navigating from one to the other; the nimble feel of portals, avoiding the barbershop pole when performing scripted finds for reports, etc.  In other words, a scenario where the user experience is diminished because of the time required by the database to execute its various functions...



            • 3. Re: Concern Regarding Database Size...

              I'd really take a look at the imported AI objects. I'd think that you're bringing in a lot of overhead with those items. AI files are PostScript. The computer has to process layers and render each object. If you've got transparency in them you're taxing the computer a lot more. JPEG's, PNG's are likely going to be much less processor intensive than AI.

              • 4. Re: Concern Regarding Database Size...

                I agree with you, David.  They're 72 dpi, but they are transparent.  I'm told that once imported into the database, it's best to then copy them from one layout to another rather than import them again for each layout; and I did that.  But the solution is still a lot heavier.  If these graphics don't increase the size of the database any more than they already have (and I don't see how they could), then I can live with 50 megs.  It's the extent to which the size is increased by user-entered text and the secure external storage of pdfs that really concerns me.


                I'm told that secure external storage of files doesn't substantially increase the database.  Is this your understanding?


                And does the size increase attributable to text entry that I described above sound about right to you?





                • 5. Re: Concern Regarding Database Size...

                  Hi D,


                  This is a small database, there should be no speed issues with text, unless you are trying to do finds on unindexed fields or something like that.  By using secure external storage the pdf's are stored outside the database keeping the database as small as possible while keeping the advantages of having the pdf's in your database, I have found no performance issues with this (and my database is 370 mb with 7 gigs of pictures externally stored).


                  About the images for the layouts, I have never used Illustrator images, I only use png's You could try to change a layout to png's and see if that helps the performance.



                  Hope that helps,


                  Best regards,


                  Ruben van den Boogaard

                  Infomatics Software


                  • 6. Re: Concern Regarding Database Size...

                    Just as a note, if you're looking for an image optimizer, yahoo's works to save a few kb per image.




                    I find that it makes more of a difference on iOS performance than desktop, but hey, if you've got time to spend on optimizations, it might be worth it.