Concern re Database Size...

[Jhestler]

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.

 

Graphics:

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...

 

D

[Kris M]

Sounds like you have graphics bloat. If you have alot of graphics intensive layouts the file size will expand as you add graphics. Their ar some ways to mitigate this.. 

If you have one graphic that is used on many layouts insert it only once and copy/paste it to the other layouts. FMP is smart enough to add the image once and use references for paste. If you have gradients and such create them small, insert them, and stretch/resize them to fit the application. FMP will only consume the size of the oiginal inserted graphic and will scale it for you without consuming the extra space.

[jbante]

FileMaker 12 files are a bit larger than nearly-equivalent FileMaker 11 files. There's more information to store about the new "design surface," but that hasn't been a significant problem yet in my experience.

 

I'm not convinced that your graphics are bloated beyond what's typical in other medium-complexity databases like what you're describing. Some things you might try in this regard:

1. If you have any gradient images, use FileMaker-native layout objects with gradients applied to them rather than inserted graphic objects.

2. For the graphic objects you do have, use PNG format images. FileMaker is optimized for handling the PNG format on layouts.

3. Consider storing your graphic objects in global container fields set on start-up rather than inserted images on layouts. I don't think this will make your database any smaller, but it does save you some work when you decide to use a different image for a given purpose, since you'll only have one place to change the image, and that change will propagate to the rest of the solution as users re-login.

 

A FileMaker file increasing in size by 10 MB per year is nothing to worry about. If you have enough text data, it could reasonably take up that much space, especially after you account for any indexes. I work on databases that increase in size by gigabytes per year without noticeable performance degradation (not due to file size, anyway). If you're still concerned, you might compare what fields are indexed and whether the indexing is "minimal" or "all"; but don't be hasty in turning indexes off, since they work to make your database faster (at the expense of also making your database bigger), and some indexes are necessary to make relationships work well.

[Kris M]

FileMaker 12 files are a bit larger than nearly-equivalent FileMaker 11 files. There's more information to store about the new "design surface," but that hasn't been a significant problem yet in my experience.

 

I'm not convinced that your graphics are bloated beyond what's typical in other medium-complexity databases like what you're describing. Some things you might try in this regard:

1. If you have any gradient images, use FileMaker-native layout objects with gradients applied to them rather than inserted graphic objects.

2. For the graphic objects you do have, use PNG format images. FileMaker is optimized for handling the PNG format on layouts.

3. Consider storing your graphic objects in global container fields set on start-up rather than inserted images on layouts. I don't think this will make your database any smaller, but it does save you some work when you decide to use a different image for a given purpose, since you'll only have one place to change the image, and that change will propagate to the rest of the solution as users re-login.

 

A FileMaker file increasing in size by 10 MB per year is nothing to worry about. If you have enough text data, it could reasonably take up that much space, especially after you account for any indexes. I work on databases that increase in size by gigabytes per year without noticeable performance degradation (not due to file size, anyway). If you're still concerned, you might compare what fields are indexed and whether the indexing is "minimal" or "all"; but don't be hasty in turning indexes off, since they work to make your database faster (at the expense of also making your database bigger), and some indexes are necessary to make relationships work well.

 

what JB said...

[Jhestler]

Thanks for your feedback, everybody!  Much appreciated!

 

J

[Wasatch2]

This is related to the main post, but from a different perspective. I create databases of plant photographs for use in remote areas, away from Internet access. In the past I have  imported images by reference from a hierarchical folder, but for simplicity (esp with Go) I want to have them embedded in a database file that can serve them to other database files.

 

As has been noted in other posts the database bloats far beyond what would be required to store the compressed jpegs. A file with 3000 images bloats to 2 Gb, whereas the folder of images is more like 500 Mb. Using the Length ( ImageContainer ) function the total "length" of the embedded files is around 500 Mb. Where is that extra 1.5 Gb coming from, and is there a way around it?