Mark Allan wrote:
Hi,
I am using SQLite on an embedded software product. The SQLite database file is
saved and read from a NOR flash chip. We have found that the writing of data to
this Flash chip can be quite slow. Therefore we need to minimise the writes
that are made by SQLite.
We have disabled the creation of the journal file when writing data to the
database and this has halved the write time. If anyone can suggest any way we
can further reduce the amount of file writes made or speed up the writing of
data to the file system then these would be gratefully receieved.
The main area where we now have a performance problem however is with deleting
records. The problem is due to the need to 'vacuum' the database when we delete
records. We do this as we need to know the size of the database file to show a
capacity readout to the user. I have been looking into the SQlite code to try
and find if there is a way in which I can read the number of 'used' pages. If I
can determine the number of used pages in the database then I can use this to
generate the capacity report and I can disable the vacuuming of the database,
which will save us much time, about 3-4 seconds!.
Please can someone advise me as to how I can determine the number of used
pages? Looking at the vacuum code it seems it needs to create a temporary file
and database to perform vacuum. Is there a way I can determine the number of
'used' pages without the overhead of having to create a temporary database on
the filesystem?
Thanks in advance for your help.
Mark
I ported some code from sqlite2 to sqlite3 that will do what you want.
You can get it from "http://unwg.no-ip.com/freepages.c". What you want
is in the function at the very bottom of the file:
int sqlite3_get_page_stats(sqlite3* db, long *lTotal, long *lFree, long *lSize);
Happy hacking!