
SQLite As The MP3 Of Data - rams
http://blog.gobansaor.com/2009/03/14/sqlite-as-the-mp3-of-data/
======
jeffesp
> Having access to that code allows me to tightly integrate it with Excel, so
> much so, that I can use Excel functions (built-in functions, VBA user-
> defined functions and 3rd party add-in functions) directly from SQLite’s
> SQL; and vice-versa, access SQL functionality via Excel "formula" calls.

This sounds really cool and is available here:
<http://blog.gobansaor.com/projects/xlite/>

------
rarestnews
I don't really work with SQLite too much (I mainly work with mySQL and in-
memory-structures, like hashes), but here are a few observations that kept me
from using it:

1\. It uses a lot of IO unnecessarily. (Actually, necessarily, due to their
motto, but 99% odf developers aren't aware that this is not necessary) SQLite-
based applications often make my hard-drive sqeak like it's on a last spin -
SQLite does A LOT of hdd activity (Chrome, Firefox, Adwords editor are
notorious for that). It's SQLites motto - 100% consistency first! journal
everything!.. but... that's my HDD and I don't want it to be dead because
SQLite thinks it's great idea to write every byte it receives to insure me
against 0.01% chance of data loss.

2\. Head-to-head runs of mySQL vs SQLite in real-world applications - well,
SQLite loses badly.. and I mean badly. I use a lot of hash-style databases
(i.e. id-value storage, 2 columns only) in some of my tests SQLite lost with
10 times worse performance against mySQL (again, if they didn't try to record
everything, like mySQL does with MyISAM - they would've got the chance).

That being said - in-memory SQLite tables are very performant! Very! But...
almost nobody uses that. As a result - Chrome/Firefox downloads phising update
and my HDD lights like a christmas tree (download Filemon from SysInternals
and watch how much Chrome or other SQLite-based engine uses your HDD for
simple data that MUST be in-memory... or at least used by in-memory cache,
like in MySQL... so your users' HDDs will eventually die because of this). I
know it's none of developers troubles to think about the fact that their
software unnecessarily uses much IO and leads to early hard drive replacement,
but still, that's a big reason why I avoid using SQLite.

I mean, there are a lot of in-memory structures that can (and should) be used,
but once the developer sees how convinient SQLite is, he jumps right on it. I
mean.. 99% of times SQLite should be used like this - load data from disk to
:memory: table (i.e. replicate it to memory), work with :memory: and
eventually dump it to drive (like once in a couple of minutes) and dump on
"Save" command. But instead every popular program uses disk-based tables. Why?
Because it's a lazy way. Who wants to add in-memory pre-caching when you can
just open a table from disk in less commands?

It's sad to see the current state of programming. Coming from an area where I
had to write programs that would fit into 64KILObytes of memory and 3.5MHZ
(that's 1000 times slower than modern single-core processor) - (ZX
Spectrum)... and we've had whole huge games in that, working in real-time,
with color graphics and almost unlimited galaxies (Elite)... it's just sad to
see how the available resources are used these days. People had to use a lot
of tricks to make things work with acceptable speeds... And now? I mean it's
3GHZ and gigabytes of data, yet I have to wait 10 seconds for Word (or
Firefox) to start up?

My main points are: 1) SQLite is convinient; 2) Disk-based SQLite (which is
used by 99% or more pieces of software) is nowhere near the possible
performance of in-memory; 3) disk-based SQLite is putting a heck of a load on
HDD (fire up FileMon during your Chrome session and be amazed); 4) SQLite is
nowhere near one-fits-almost-all solution, like mp3 is; 5) even so it will
gain more popularity because it's convenient for developers.

I have MySQL table with 72billion denormalized items (72million rows) and it's
in realtime application... and it performs! I can't even imagine what would
SQLite be like with that kind of data.

Still, please note that I'm not saying SQLite is bad, I'm saying the default
route almost all devleopers take with it has some serious issues and yet
nobody seems to care.

~~~
edgeztv
I second the IO issue - it's alarming to have the HDD always churning when I
have Chrome open. Assuming SQLite is the culprit, that's a good reason to
think twice about embedding it into an application.

~~~
rarestnews
BTW, if you turn off phishing protection - that lowers HDD usage
significantly.

(But, reasonably enough, by "turning off phishing protection" you have be more
attentive to what you're doing online)

------
jf
Did I miss something obvious?

How does one configure Excel to use SQLite?

~~~
rodrigo
<http://blog.gobansaor.com/projects/xlite/>

I've used sqlite from MS Access, using sqlite ODBC driver; just for fun
though, havent done anything useful with it yet.

------
henning
Can someone refresh my memory of how SQLite handles concurrency? Doesn't it
block on writes or something?

For a long time I've thought a spiritual successor to Access, Filemaker, etc.
based on SQLite would be a great idea.

~~~
senko
By default it does deferred transactions (exclusive lock is only acquired when
you need to commit), but if you use insert/update/delete outside a
transaction, each statement is it's own transaction.

Locking is database-wide, although IIRC there's some branch that can do table-
level locking.

~~~
jmtulloss
As far as I know, SQLite's version of "table-level locking" is to put each
table in a different database file and ATTACH them all to one database
connection.

------
stcredzero
To be analogous to MP3, it would need:

    
    
        - Enabled distribution by overcoming bandwidth issues
        - Has been superseded by superior technology,
            but the majority think it's good enough already
        - Is seen as "open" even though it's proprietary
        - Because of the above, has become a defacto standard
    

How many of the above does SQLite fit?

------
jacoblyles
This actually might be good for a problem I ran into today. I'm distributing
some source code with a large data file, one that we would like to store in a
SQL database for ease of use. However, we don't know that the user machines
will have MySQL installed. Would it make sense to use SQLLite in this
instance? It sounds like you don't have to install it.

~~~
jf
You do need to "install" SQLite, but not in the traditional sense. Most likely
all you need to do is include a library that knows how to read and write to
SQLite files. Worst case, you'll need to ship your code with a small static
binary and call out to that.

------
vicaya
Yeah, SQLite is as lossy as MP3 :)

FWIW, I've lost some of my mails at least twice in Apple Mail due to corrupt
SQLite files, but that maybe due to OS X's crazy fsync implementation.

------
budwin
different tools for different tasks. as far as setup time goes, sqlite wins by
a long shot.

