Try making your date a REAL and using the Sqlite date and time
functions. You will use extra space for the rowid, the key of the row
and for the b-tree index.
You would expect the indexed rows to be about double the raw text data
since the numbers are 64 bit FP.
Corey Nelson wrote:
> I'm developing some software that helps with day trading. I need to store
> years worth of tick prices. At first I was going to write a library that
> would write and read this information to and from files. Then I thought
> "don't be silly", this is the sort of thing databases were made for. I have
> little experience with databases but I thought surely I could find a small,
> fast, easy to use database I could use for this purpose.
>
> It didn't take me long to get some test data into an SQLite3 database file.
> But there's a problem, the database file is almost three times bigger than
> storing the information in text files the way I had planned. Even writing
> the contents of the database to text produces a text file less than half the
> size of the database file.
>
>
>>sqlite3 Ticks.db ".dump"
>
> BEGIN TRANSACTION;
> CREATE TABLE StockName (date TEXT UNIQUE ON CONFLICT REPLACE, price REAL);
> INSERT INTO "StockName" VALUES('1202680806000',96.18);
> INSERT INTO "StockName" VALUES('1202680807000',96.16);
> INSERT INTO "StockName" VALUES('1202680821000',96.15);
> [...]
> INSERT INTO "StockName" VALUES('1202767171000',96.71);
> INSERT INTO "StockName" VALUES('1202767174000',96.7);
> INSERT INTO "StockName" VALUES('1202767197000',96.68);
> COMMIT;
>
>
>>sqlite3 Ticks.db "select rowid,* from StockName" > Ticks.txt
>>more Ticks.txt
>
> 1|1202680806000|96.18
> 2|1202680807000|96.16
> 3|1202680821000|96.15
> [...]
> 12278|1202767171000|96.71
> 12279|1202767174000|96.7
> 12280|1202767197000|96.68
>
> Ticks.db = 1334272 bytes
> Ticks.txt = 613702 bytes
>
> I would expect the database file to store a bit of "extra" data but it's
> 2.17 times bigger than the text file! That means the "extra" data uses more
> room than the stuff I'm interested in. Is this size difference normal and to
> be expected or am I doing something wrong? Is this just the price I have to
> pay for the convenience of a database?
>
> I don't think it's relevant but my software is written in Java and I'm using
> SQLiteJDBC from http://www.zentus.com/sqlitejdbc/. For the date I'm just
> storing the long returned by java.util.Date.getTime() since it is easy to
> work with and I figured that the string would be shorter than a normal date
> string.
>
> Thanks for reading.
>
> Corey
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
