3 ms·

Using SQLite for great science, part 1 (or 3, I guess). | Stavros' Stuff

24 points by iamelgringo 16y ago

gxti 16y ago
SQLite is so hardcore about being totally unbreakable that they have a section in their documentation dedicated to explaining all the horrible things the OS has to do just to get it to fail: http://www.sqlite.org/lockingv3.html#how_to_corrupt http://www.sqlite.org/lockingv3.html#how_to_corrupt Reading about their testing process is also informative: http://www.sqlite.org/testing.html http://www.sqlite.org/testing.html
lsb 16y ago
3M rows, 70M relationships? Sounds like it could fit in a hash table in memory in your programming language of choice.
- regularfry 16y ago
  Sure. Then you've got to persist it.
- gaius 16y ago
  You can use :memory as a filename for SQLite and that's just what it'll do. Of course if it's in your OSs filesystem cache then it is in memory even if it's on disk. Or you can roll your own and write your own query language too...
zandorg 16y ago
I used SQLite until I found a query that locked it up seemingly forever. It had 2 inner joins. It worked with 1 inner join - NOT 2. The author of SQLite said "Not my problem buddy, it's yours". So I'm switching to PostGRE (which CAN handle those 2 inner joins) or a Microsoft SQL.
- forinti 16y ago
  Postgresql and MS SQL aren't really alternatives to SQLite. Maybe you just had the wrong tool to begin with.
- silentbicycle 16y ago
  That's a query optimizer complexity trade-off. SQLite is targeted for embedded usage, and a smarter query optimizer would make SQLite much larger, cutting into one of its main strengths. You're better off using SQLite as a scaled-down RDBMS and moving to postgres when necessary. Lua's design makes similar trade-offs, and they comment on this in http://www.lua.org/doc/sblp2005.pdf http://www.lua.org/doc/sblp2005.pdf: "For instance, just the control-flow analysis of Bigloo, an optimizer Scheme compiler, is more than ten times larger than the whole Lua implementation." It's the same reason the core language doesn't have regular expressions. (Well, that, and LPEG.)
deleted 16y ago
[deleted]
sshconnection 16y ago
1000 writes in 5 seconds for mongo? Running 1.4, I get ~2500/sec on an old P4 desktop that I was using to test on. I've only recently started working with mongo, and I have no experience working with 1.3, the unstable development version that he lost his data using. I'm having a hard time buying into his comparison.

24 points by iamelgringo 16y ago

gxti 16y ago

SQLite is so hardcore about being totally unbreakable that they have a section in their documentation dedicated to explaining all the horrible things the OS has to do just to get it to fail: http://www.sqlite.org/lockingv3.html#how_to_corrupt http://www.sqlite.org/lockingv3.html#how_to_corrupt Reading about their testing process is also informative: http://www.sqlite.org/testing.html http://www.sqlite.org/testing.html

lsb 16y ago

3M rows, 70M relationships? Sounds like it could fit in a hash table in memory in your programming language of choice.

regularfry 16y ago
Sure. Then you've got to persist it.
gaius 16y ago
You can use :memory as a filename for SQLite and that's just what it'll do. Of course if it's in your OSs filesystem cache then it is in memory even if it's on disk. Or you can roll your own and write your own query language too...

zandorg 16y ago

I used SQLite until I found a query that locked it up seemingly forever. It had 2 inner joins. It worked with 1 inner join - NOT 2. The author of SQLite said "Not my problem buddy, it's yours". So I'm switching to PostGRE (which CAN handle those 2 inner joins) or a Microsoft SQL.

forinti 16y ago
Postgresql and MS SQL aren't really alternatives to SQLite. Maybe you just had the wrong tool to begin with.
silentbicycle 16y ago
That's a query optimizer complexity trade-off. SQLite is targeted for embedded usage, and a smarter query optimizer would make SQLite much larger, cutting into one of its main strengths. You're better off using SQLite as a scaled-down RDBMS and moving to postgres when necessary. Lua's design makes similar trade-offs, and they comment on this in http://www.lua.org/doc/sblp2005.pdf http://www.lua.org/doc/sblp2005.pdf: "For instance, just the control-flow analysis of Bigloo, an optimizer Scheme compiler, is more than ten times larger than the whole Lua implementation." It's the same reason the core language doesn't have regular expressions. (Well, that, and LPEG.)

deleted 16y ago

[deleted]

sshconnection 16y ago

1000 writes in 5 seconds for mongo? Running 1.4, I get ~2500/sec on an old P4 desktop that I was using to test on. I've only recently started working with mongo, and I have no experience working with 1.3, the unstable development version that he lost his data using. I'm having a hard time buying into his comparison.