5 ms·
> People who understand SQL can employ the sqlite3 command-line shell to analyze large datasets. And a bit further down: > SQLite database is limited in size
by lucb1e 10y ago
> People who understand SQL can employ the sqlite3 command-line shell to analyze large datasets.
And a bit further down:
> SQLite database is limited in size to 140 terabytes [...] if you are contemplating databases of this magnitude [use something else]
Yeah no. "Large datasets" here means a few megabytes. I figured that out the hard way:
I had a database of about 70 megabytes and ran a query with "COUNT(a)" and "GROUP BY b" on it. This makes it write multiple gigabytes to /tmp until it goes "out of disk space" (yeah /tmp on my ssd isn't large).
I heard nothing but awesome and success stories about SQLite until a few weeks ago when this fiasco happened. I still like SQLite for its simplicity and last week I used it again for another project, but analyzing "large" datasets? Maybe with a simple SELECT WHERE query, but don't try anything more fancy than that when you have 100k+ rows.
- qwertyuiop924 10y agoThat seems odd. Have you looked at the SQL docs for why this might be? Also, have you compared this to what other DBs do?
- pmorici 10y agoHow you considered you might have used it wrong? If everyone else says it works great and you use it and have an issue my first thought would be I must have done something wrong not this sucks everyone else must be wrong.
- lucb1e 10y agoSound logic, but I didn't think to myself "gee everyone else is wrong". I just noticed SQLite did something I've never seen another database do and figured it's not made for this.
- vacri 10y agoI find this happens quite often - once you stray from the most common path for $AWESOME_TOOL, you start finding little oddities here and there. It's not that 'everyone else is wrong', it's just that you're off the beaten track.
- cypherpunks01 10y agoI'm sure if you posted the EXPLAIN here, someone might be able to figure out why it did that. Were you able to test the exact same query on a different database?
- lucb1e 10y agoI was going to, but something else got in the way and I ended up not running the query again. Over the years I've had my share of complex queries with subqueries and aggregates, both for class and for my own projects, but never have I encountered 70MB exploding into multiple gigabytes (I don't even know what its final size would have been). I guess I could have used EXPLAIN and dug into it, but never having had this before I figured it was SQLite not being made for it.
- kbaker 10y agoOf course, "large" is not about the data necessarily, but about the analysis of the data. It sounds like you presented sqlite with a query which would need more than the resources of your computer in order to execute as you specified. This can be really easy to do accidentally on any DB engine with something like a cartesian product. It is true that sqlite doesn't have as good of a query plan optimizer as larger RDBMSes, and is a little lower-level, but the tradeoff of having the simplicity is that you must understand a little more of the internals to design more complex queries.
- dorfsmay 10y agoFor analytics, monetDB is my new small local db.
- blahi 10y agoMonetDB lite embeds in R like rsqlite.
- thom 10y agoI've used SQLite with a 5GB database and tables up to about 15m rows, and while I've certainly managed to chew up a lot of disk (same as SQL Server or Postgres), it's not the day to day experience, even with quite complex queries. All you can do is EXPLAIN QUERY PLAN, or perhaps look into putting temp files in RAM, if that is more plentiful: http://stackoverflow.com/a/19259699/24618 http://stackoverflow.com/a/19259699/24618
- dijit 10y agoFWIW MySQL performs in exactly the same way, any temporary tables by default go to /tmp this includes whatever you stick in parenthesis. `select * from table where account_id in (select id from accounts where name like 'Fred%');` for example
- yAnonymous 10y agoIf you're like most other developers, your tables probably look like Excel sheets. No wonder it performs badly.
- yread 10y agoJust last week I played with a project that involved inserting 40M words (and their tuples, triplets, quadruples, ...) into in memory SQLite database. Even though the db was about 600MB a query which grouped the tuples by their frequency finished in under 5s.
- Dan_Kennedy 10y agoPerhaps this is a bug. If you post the query and database schema on the sqlite mailing list or here we can take a look easily enough. If you can upload the database somewhere we can try to reproduce it. Mailing list: http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
- lcarlson 10y agoWhatever your issue was, it most likely has to do with our query planning or lack of indexes. I've used sqlite with success on many gigabyte datasets.