3 ms·

Agreed. And the database can grow surprisingly large (multi GB with many millions of rows) and it still performs quite well.

by DNeb 15y ago

kellishaver 15y ago
This surprises me. Though I've used it with great success for several small projects, my only experience with SQLite on anything remotely large was a Rails app with maybe a million records in the DB. Over time, performance became very slow and switching to MySQL made a world of difference. At the time, I attributed it to SQLite, but now I'm wondering if it was Rails or (more likely) my inexperience at optimizing performance of the app at that time. It was about three years ago, though, so I am sure performance in SQLite (and Rails, too, for that matter) has also likely improved a lot during that time.
- lsb 15y ago
  In SQLite, autocommit is on, so if you weren't explicitly setting your transactions, you might have been commit after each update. Also, the standard disk cache is 2MB, so if you think your DB should have more memory than that, up it with default_cache_size.
  - kellishaver 15y ago
    Ah, I wasn't, so that combined with pilif's comments regarding the file being locked for writes would make a lot of sense as to why things were slowing down. Thanks for the info.
- pilif 15y ago
  Write operations to a SQLite file lock the whole file. It's possible that your rails app had enough users doing enough writes so that the processes had to constantly wait. As long as you are the only user, performance should be constant regardless of file size (minus fragmentation issues)
- brazzy 15y ago
  It depends a lot on the complexity of queries. Implementing a B-tree is not easy but no black magic either (I did it for my diploma theses). Same for a hash join. And these two things are really all you need to have reasonable performance for simple selects and joins on tables of almost any size. But if your queries get more complex, the query execution plan starts to make a huge difference - and a query optimizer is black magic, as anyone who's wrestled with Oracle's can attest. I doubt SQLite can compete in that area.

Agreed. And the database can grow surprisingly large (multi GB with many millions of rows) and it still performs quite well.

by DNeb 15y ago

Agreed. And the database can grow surprisingly large (multi GB with many millions of rows) and it still performs quite well.

kellishaver 15y ago

This surprises me. Though I've used it with great success for several small projects, my only experience with SQLite on anything remotely large was a Rails app with maybe a million records in the DB. Over time, performance became very slow and switching to MySQL made a world of difference. At the time, I attributed it to SQLite, but now I'm wondering if it was Rails or (more likely) my inexperience at optimizing performance of the app at that time. It was about three years ago, though, so I am sure performance in SQLite (and Rails, too, for that matter) has also likely improved a lot during that time.

lsb 15y ago
In SQLite, autocommit is on, so if you weren't explicitly setting your transactions, you might have been commit after each update. Also, the standard disk cache is 2MB, so if you think your DB should have more memory than that, up it with default_cache_size.
- kellishaver 15y ago
  Ah, I wasn't, so that combined with pilif's comments regarding the file being locked for writes would make a lot of sense as to why things were slowing down. Thanks for the info.
pilif 15y ago
Write operations to a SQLite file lock the whole file. It's possible that your rails app had enough users doing enough writes so that the processes had to constantly wait. As long as you are the only user, performance should be constant regardless of file size (minus fragmentation issues)
brazzy 15y ago
It depends a lot on the complexity of queries. Implementing a B-tree is not easy but no black magic either (I did it for my diploma theses). Same for a hash join. And these two things are really all you need to have reasonable performance for simple selects and joins on tables of almost any size. But if your queries get more complex, the query execution plan starts to make a huge difference - and a query optimizer is black magic, as anyone who's wrestled with Oracle's can attest. I doubt SQLite can compete in that area.