DEC

Concern Regarding Database Size...

Discussion created by DEC on Feb 22, 2013
Latest reply on Jun 26, 2013 by mikebeargie

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

Outcomes