Database file optimization
Avoid database schema changes.
possible, avoid changing the schema (table structure) of a database
once you’ve added data into the database’s tables. Normally a database
file is structured with the table definitions at the start of the
file. When you open a connection to a database, the runtime loads
those definitions. When you add data to database tables, that data
is added to the file after the table definition data. However, if
you make schema changes, the new table definition data is mixed
in with the table data in the database file. For example, adding
a column to a table or adding a new table can result in the mixing
of types of data. If the table definition data is not all located
at the beginning of the database file, it takes longer to open a
connection to the database. The connection is slower to open because
it takes the runtime longer to read the table definition data from different
parts of the file.
Use the SQLConnection.compact()
to optimize a database after schema changes.
If you must
make schema changes, you can call the SQLConnection.compact() method
after completing the changes. This operation restructures the database
file so that the table definition data is located together at the
start of the file. However, the compact() operation
can be time-intensive, especially as a database file grows larger.