Next, be aware there seems to be a bug - if your database has Container fields that use Open Storage, this makes the tool so slow that it is unusable (I waited over 3 hours before giving up). . A workaround is to add the -exclude_containers option which restores normal speed.
I wanted to know the size of each of my tables, using the -sortBySize command which will show the size of the top 3 biggest tables
Some of that was expected, but I was surprised to learn I was keeping over 900MB of sent email in the database.
To dig deeper, add the -target_tablename option with the name of the table, and instead of a list of tables, you get a list of fields from that table, sorted by size:
This shows me that nearly the entire 900+ MB of space is being taken up by one field, BodyHTML.
Looking into this further, I realized that I've got over 5 years of outgoing email in this table, and since we have many many backups for these emails, keeping that inside FileMaker is just taking up space.
Using this tool reveals some interesting things about data size.
I have one table which has about 10 million records and is about 1.5GB in size.
Here are the top 4 largest fields:
FieldName
FieldSize
Units
Modified
178
mb
Created
148
mb
Serial
78
mb
Date
72
mb
The Created and Modified fields (which are both timestamps) are taking up about 20% of the entire table size.
While these fields are very useful for development and debugging, that's a lot of space being taken up.
I notice that my "Date" field (which is just Date, not a Timestamp) takes up less than 50% of the space.
I wonder if there's a better way to do it? For example, what if i stored the Modification timestamp as a Number field instead of a Date field - would that save on space?
Testing indicates that you can save some space by storing a TimeStamp field as a number.
Also, since FileMaker stores numbers as text, you can get further reductions in size by shortening that number in various ways:
remove an offset (in this case, by subtracting the timestamp for 2000-01-01, which is 63082281600).
reducing precision by dividing the timestamp by 100 and keeping only the integer portion, reducing accuracy to a little under 2 minutes
Field Storage Type
Size (MB)
Size Reduction Ratio
TimeStamp
178
1.0
stored as Number
96
1.8
Number (subtract 63082281600 )
78
2.3
Number (subtract 63082281600, divide by 100 )
60
3.0
To get back to the human-readable timestamp, simply create an Un-stored Calculated field which reverses the calculation.
Conclusion
Timestamp fields (such as Creation and Modification) have a fairly heavy cost in terms of storage, but can also be very useful for debugging. By storing them as numbers with reduced precision, you can cut the storage size by about 2-3x.
Are those timestamp field being Indexed? If so, you could try turning OFF indexing, closing the file, and see if that changes anything.
When I look in FMP 21.x it shows that I can NOT do minimal indexing for TimeStamp or Date fields - which makes me suspect these are stored as numbers. Am I wrong?
These are unindexed, but if I were doing debugging work I would probably index them temporarily to speed things up.
FMDeveloperTool has an option to show index sizes:
[-query_index | -qi] Whether to query/sort by field index size. If argument added, will query/sort by field index size instead of field data size. Note that for querySize command, this option has to be bound with --target_fieldName, it won't take effect if no target field name is provided. To see all fields index list, use sortBySize command.
Right, I was surprised to see that storing the timestamp as a Number field reduced size (from 178 to 96) which suggests they aren’t stored as numbers internally? Not sure.
