Weekly edition	Kernel	Security	Distributions	Contact Us	Search
Archives	Calendar	Subscribe	Write for LWN	LWN.net FAQ	Sponsors

Berkeley DB 4.5 available

Posted Sep 27, 2006 20:09 UTC (Wed) by yodermk (subscriber, #3803)
In reply to: Berkeley DB 4.5 available by mikov
Parent article: Berkeley DB 4.5 available

I got turned off to SQLite when I heard that it stored *all* data types, including numbers, as strings. You call *that* efficient? Or did they fix that?

I'm starting a project using HSQL as its embedded DB. Seems like a good choice so far.

(Log in to post comments)

Posted Sep 27, 2006 20:12 UTC (Wed) by trutkin (guest, #3919) [Link]

That doesn't make sense as the sqllite generated files are binaries.

Berkeley DB 4.5 available

Posted Sep 27, 2006 20:26 UTC (Wed) by dark (✭ supporter ✭, #8483) [Link]

I don't see why it would be inefficient. Most numbers in databases are pretty small, so they don't take much space even compared to a binary representation, and I bet there are wins from treating all data types uniformly.

I'll start caring about it after I start caring whether my numbers are "int" or "smallint" in mysql :)

Berkeley DB 4.5 available

Posted Sep 28, 2006 9:26 UTC (Thu) by hawk (subscriber, #3195) [Link]

It's obvious that if it does indeed store numbers as their string representation, that certainly is a big waste of space.

For instance, which is better use of 4 bytes, to be able to store numbers 0-9999 or 0-4294967295 (if we take unsigned integers as our example)?

(Or if you want really small numbers as your example, with one byte you get 0-9 vs 0-255.)

You do however have a point, but what I'd say is not that it's efficient, but rather that for many uses it may not matter that much that it's awfully inefficient.

Berkeley DB 4.5 available

Posted Sep 27, 2006 20:36 UTC (Wed) by bronson (subscriber, #4806) [Link]

That was true in v2, not in v3. See http://www.sqlite.org/version3.html in the "Manifest Typing and Blob Support" section. Or see http://video.google.com/videoplay?docid=-5160435487953918... where the author somewhat explains why he made this design decision.

Even after watching the video I don't understand why he wanted this loosey-goosey datatype "feature". Something about speeding up development like dynamic typing? Anyhow, it hasn't gotten in my way.

Berkeley DB 4.5 available

Posted Sep 27, 2006 20:36 UTC (Wed) by mikov (subscriber, #33179) [Link]

SQLite version 3 stores integers, floats and BLOBs as binary. Version 2 used to store everything as ASCII; while that incurred (usually small) overhead, it was a very simple and ellegant design.

In any case, unless you are manipulating large chunks of data, the overhead of conversion to and from ASCII is often quite negligible compared to everything elese that has to happen.

You also fail to consider the fact that reading a binary integer from disk in Java (HSQL is written in pure Java) requires reading the bytes separately and combining them - that is not that different from converting ASCII to string - both operations have negligible overhead. PostgreSQL's JDBC driver also transmits almost all values as text 9even though the wire protocol allows binary) and so far this isn't seen as a serious limitation.

At one time in the past I looked at HSQL and from their website and documentation I couldn't get a good idea of the internal design. That discouraged me from looking further. By comparison SQLite has several in depth document explaining all aspects of the architecture in complete detail.

Of course, if you language is Java, SQLite is not a serious option - there is a JDBC driver for SQLite, but AFAIK it is not very complete.