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
