DBF File Optimization
DBF file optimization or DBF structure optimization procedure claims to reduce the size of a DBF database file. How so? Why DBF file size reduction is possible in the first place? .
Basic Principles of DBF File Structure
The size of a DBF file mostly depends on the internal structure of the database, not on the size of the exact data this database holds. This happens because every field of the database reserves a particular amount of disk space regardless of whether the actual data are stored in that field or not.
For example, if the field type is defined as CHARACTER(40), the length of this field is 40 bytes regardless of whether this field holds “Hello, world!”, “Chicago” or “”. Ditto the other data types. Every numeric type has its size in bytes, and this amount of memory will be taken even if the actual number doesn’t require all 4 or 8 bytes reserved for the field. That’s how things work historically.
Why Optimizing DBF Files?
Many older databases often have a lot of unused space. Sometimes the size of each field exceeds the really required amount twice or more. Developers add the margin to avoid future problems or when they don’t know exactly what data are to be kept in the database. Apparently, you DO know what data are stored in the database now and therefore can easily reduce the size of the database.
How to Reduce the Size of DBF
Now, as you know how the data are stored within a DBF file, you should better understand how to optimize it. The unused space in each field can be trimmed. This results in 20-80% gain in size! In fact, that’s what DBF Viewer 2000 does.
The program loads the DBF file you want to optimize, scans its structure and for each field calculates the maximum length of data actually stored in that field across all records in the database. Then, DBF Viewer 2000 simply trims all the field types to the maximum size found this way.
In this simple example we have easily won 33 bytes or about 27% of original size of the database. This is tiny, yes. But when the entire database takes 2 GB of space, 27% of it means over 550 MBs of free space!
Is Optimization of DBF File Safe?
Yes. A database optimized this way works absolutely identically to the original file. In fact you may even notice some performance increase due to reduced size of the database. However, there is one thing you should take into account while optimizing your DBF files this way.
The optimization algorithm assumes there is no data longer than it has already found in the database while scanning the records. But the algorithm cannot guarantee such data will not be added later. And since the field space is already truncated, so is the new information. This is a rare case, of course, but you have to be prepared.
Optimization via Command Line
"c:\Program Files (x86)\DBF Viewer 2000\dbview.exe" d:\DBF\myfile.dbf /optimize
See also: DBF to Excel, DBF to SQL, DBF to CSV, Filtering records, Sorting records in dbf file, Command line options