On 14/09/12 22:56, Clemens Ladisch wrote:
Elefterios Stamatogiannakis wrote:
On 13/09/12 23:02, Clemens Ladisch wrote:
For my main workload (OLAP) this can make an enormous difference!
OLAP isn't quite the typical SQLite use case. But do you have any
numbers (which would help deciding whether to accept this patch)?
Concerning the "numbers", i'll run some timings.
Concerning OLAP being the typical SQLite use case. I haven't chosen
SQLite for OLAP lightly. When the choice was made (some years ago) we
had benchmarked other DBs too (MySQL, Postgres).
Things may have changed from when we did the benchmarks, but i suspect
that if we ran the same tests again the final choice would remain the
same. Some more details concerning the things that we had found follows.
MySQL's query execution engine was very fast for simple selects, counts
etc. When the queries became more complex, MySQL tended to "explode" due
to materializing nested queries to the disk or other query optimizer
stupidities.
Postgres, which i hold dear in my heart, was a lot closer to SQLite's
behaviour (it also runs single threaded for single OLAP queries).
Nevertheless it was slower than SQLite for queries involving UDFs and on
some complex queries it could take many times more to finish a query
than SQLite.
In the end we didn't care that much about raw performance, but about UDF
flexibility/speed, and to have a reliable and predictable query
planner/optimizer.
Concerning the query optimizer, in my opinion and by watching other more
experienced people than me writing queries. What counts more is not the
cleverness of the query optimizer but being able to predict what a
reliable optimizer will do.
There are a number of things that i miss from Postgres e.g. nested
tables and the new JSON storage in 9.2. But using SQLite's UDF API (via
exceptional APSW [*] ) i have created workarounds for them in madIS [*].
So i don't care that much.
What i would really like to have in SQLite concerning OLAP, would be
bigger pages, and internal page compression in a similar manner that
column stores do [^]. This would greatly alleviate the storage pain of
using denormalized DBs which is a must for OLAP.
In addition, this feature would bring SQLite's engine very close to also
being able to function as a document store while at the same time
speeding up its relational processing (due to having smaller DBs on the
disk).
Kind regards,
lefteris
[*] APSW: http://code.google.com/p/apsw/
madIS: http://code.google.com/p/madis/
[^] Like http://en.wikipedia.org/wiki/RCFile . Another idea would be for
each column to have a dictionary at the start and then the column's data
would reference the column's dictionary.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
