On 2014/05/28 09:25, Hadashi, Rinat wrote:
Hi
My databases are very big (almost 100 GB).
I am looking for a compression solution.
Did anyone have an experience with reading a compressed database?
What was the degradation in performance of queries?
Severe.
Well, this depends - if the table consists of rather large data values (which do not form part of an Index), compressing those saves
a lot of space with no significant loss in performance. If however you try to search (or SELECT WHERE....) using such a compressed
field, the performance penalty is severe. Just imagine, the DB engine has to decompress every item in the table to match it against
the query requirements (unless you need an exact match and can search with an already-compressed specifier).
If the table doesn't contain any large fields but simply very many records, then any compression is useless. (In my experience, the
LZ and GZ algorithms start paying dividends in size reduction after about 70 characters of standard English language information.
(Very random info takes longer and very repetitive info pays quicker).
If you do have tables with large data fields and want to experiment with it - you can use one of the SQLite projects we've designed
from here:
http://www.rifin.co.za/software/sqlc/
(Just get the thing from the downloads page)
It's just a DB manager but it adds a lot of extra SQL functions, all math functions etc. and encryption and compression functions,
so you could for instance do stuff like this:
UPDATE sometable SET ItemDescription = Encode(ItemDescription, '') WHERE ID =
nnn;
or
SELECT Decode(ItemDescription, '') FROM sometable WHERE ID = nnn;
Encode/Decode takes 2 parameters, the first being the text to be encoded/decoded and the second a password. It encrypts and
compresses the data (actually first compress then encrypt it).
If the password is empty (upon encryption) it will only compress, etc.
It's all well-explained in the SQL code-hinting windows.
Those algorithms are adapted from the fastest code around and optimized for speed rather than compression size, but it does decent.
Anyway, the point is you can see what kind of compression ratios and what kind of speeds you can expect when using data compression
in a table like yours and whether it is feasible or not.
(PS1: Needless to say, please try it out on a copy of your database and not the
in-use versions).
(PS2: That system's support is not via this list, so if you have questions about it, kindly mail me direct and not bore these people
with it).
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
