Re: [sqlite] database size (again)

[Max Vlasov]

On Tue, Dec 7, 2010 at 6:57 PM, Laszlo Nemeth wrote:

> Hi,
>
> Question: is it possible to recompile sqlite to force the
> representation of integers to be 4 bytes, and that of floats to be
> also 4 bytes. I would like to have no observable change in the
> behaviour of sqlite.
>
>

Since integers are effectively packed in sqlite and I suppose you can not
map you tick to rowid (I guessed it from the name), the only possible
improvement could be floats. You could borrow this idea:
http://stackoverflow.com/questions/2775854/map-a-32-bit-float-to-a-32-bit-integerto
map it in your reading/writing code to save space for floats. In worst
case every 8 bytes float will be 4(5)-byte integer (5 possible due to the
internal packed format of sqlite), so maybe you will save 10-15 bytes per
record (I added also indexes)

Max Vlasov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database size (again)

[Jim Wilcoxson]

A lot of the SQLite overhead is going to be in the stuff surrounding your
actual data; I'd be surprised if you saved much space by using fixed-size
ints vs the varints used by SQLite.  You didn't mention about indexes; if
you have any, they will take a lot of space because your row size is so
small.

Maybe write your own VFS for SQLite?

Definitely run sqlite3_analyzer before deciding anything.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup


On Tue, Dec 7, 2010 at 10:57 AM, Laszlo Nemeth wrote:

> Hi,
>
> I have a database (6 in fact) of high-frequency data
>
> create table eurusd (tick integer not null, bid float not null, ask
> float not null);
>
> with 80M records currently and growing, freshly inserted, no deletions
> will ever take place, the schema will never change, and neither of the
> fields can be null. The size is already 3.6G (I put an index on it
> after bulk insert), which is a bit too much for me considering that
> the existing infrastructure (ie binary file 12bytes per record) is
> 800M (which fits into memory and I don't even need to index).
>
> Having checked older posts on sqlite-users, I noticed that this issue
> comes up frequently and normally the problem is either wrong choice of
> datatypes (ie text instead of integer), or unnecessary indices. None
> of which applies here.
>
> Question: is it possible to recompile sqlite to force the
> representation of integers to be 4 bytes, and that of floats to be
> also 4 bytes. I would like to have no observable change in the
> behaviour of sqlite.
>
> I
> (1) am quite comfortable with the hacking,
> (2) understand that the database will no longer be platform
> independent, nor compatible with anything else,
> (3) tried to run the analyser to see if there is something fishy with
> half full pages, but it wants tcl8.6 and haven't gotten around to
> install it (will do),
> (4) also checked the file format document, but that didn't give me any
> immediate hint how to achieve what  I'd like, though I only skimmed it
> through.
>
> The point of doing this is that I get a smaller db, and I still get
> all the beautiful machinery built for sqlite.
>
> Any suggestions, a complete solution, or  a "no  that's not possible,
> because..." will be much appreciated.
> Thanks, Z
>
> ___
> 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

[sqlite] database size (again)

[Laszlo Nemeth]

Hi,

I have a database (6 in fact) of high-frequency data

create table eurusd (tick integer not null, bid float not null, ask  
float not null);

with 80M records currently and growing, freshly inserted, no deletions  
will ever take place, the schema will never change, and neither of the  
fields can be null. The size is already 3.6G (I put an index on it  
after bulk insert), which is a bit too much for me considering that  
the existing infrastructure (ie binary file 12bytes per record) is  
800M (which fits into memory and I don't even need to index).

Having checked older posts on sqlite-users, I noticed that this issue  
comes up frequently and normally the problem is either wrong choice of  
datatypes (ie text instead of integer), or unnecessary indices. None  
of which applies here.

Question: is it possible to recompile sqlite to force the  
representation of integers to be 4 bytes, and that of floats to be  
also 4 bytes. I would like to have no observable change in the  
behaviour of sqlite.

I
(1) am quite comfortable with the hacking,
(2) understand that the database will no longer be platform  
independent, nor compatible with anything else,
(3) tried to run the analyser to see if there is something fishy with  
half full pages, but it wants tcl8.6 and haven't gotten around to  
install it (will do),
(4) also checked the file format document, but that didn't give me any  
immediate hint how to achieve what  I'd like, though I only skimmed it  
through.

The point of doing this is that I get a smaller db, and I still get  
all the beautiful machinery built for sqlite.

Any suggestions, a complete solution, or  a "no  that's not possible,  
because..." will be much appreciated.
Thanks, Z

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users