customsetr.blogg.se - Db browser for sqlite size of table

#Db browser for sqlite size of table 64 Bit#

one storage mode byte), depending on their value. SQLite stores integer values individually into 2-9 bytes (incl.

A datetime_delta column, storing the difference from the last row with a filled datetime column.

A datetime integer column, only filled for every (say) 1000 records.

A sequentially numbered integer primary key.

In that case, you would use three columns: In this case, the datetime values are great for this, if they can be stored in order into the database. I have never tried this in SQLite in practice, but: some data is more compactly stored by storing only the difference to the previous row. So you can create a SQLite table without ROWID, as follows: CREATE TABLE "Data" ( Since you were talking about a UNIQUE constraint for the datetime column, this column seems a good candidate for the primary key (which always has a unique constraint attached).

#Db browser for sqlite size of table 64 Bit#

The ROWID is an internal primary key that SQLite tables have by default, taking 64 bit per record. Here are two that seem appropriate for your case: WITHOUT ROWID tables Is it possible to recude the size of the database or that is all one can get from it? So it turns out that the internals take 16%, PK takes additional 19% but still reduces the total size, and the unique contraint takes almost 40%! *** Page counts for all tables and indices separately Is it the SQLite internals which make that big overhead in size?Īs a side note, setting Primary Key on the dateTime field reduces the size of the example data file by 0.3kk bytes but adding unique constraint increases it by 3kk bytes making it about 8kk in total which is much bigger than the CSV one. Compression (available in DB Browser for SQLite) does not change anything.ĬREATE TABLE "Data" ( "dateTime" INTEGER, "value1" REAL, "value2" REAL, "value3" INTEGER, "value4" INTEGER ) The CSV file is about 6kk bytes in size but the resulting DB file is about 5kk which is almost the same. The dateTime can be stored as 8-byte integer (c# DateTime.Ticks) which should reduce the size to about 26 bytes per one line which is almost twice less than in CSV format.įor an experiment I tried importing 142,157 lines of data.

As far as I understood the SQLite types definitions described at it should take about the same in the database:Ģ3 byte datetime text + 8*2 reals + 1*2 integers = 41 bytes The example string in CSV format takes 44 bytes (43 chars + new line). I was expecting to reduce the size considerably when migrating to SQLite but to some reason am getting some different results. I'm considering to use SQLite database for a C# application which deals with large volumes of data series.