1

I have a table holding some data about some entities. When I had 250,000 entities sqlite3 worked reasonably fast, but after adding 100k more (so now i have 350k entities in one table), it significantly slowed down (at least 2-3 times slower!).

What could be the cause, and how can I overcome that? Note: I intend to add a few million entities to the table.

a tiny update: Now that I've made some queries, it is reasonably fast again,even after closing sqlite3 and the shell... I find that a bit weird, and I'm not too sure if it's because there's something loaded to my RAM, hard drive's cache or some sqlite's own database internal representation (db file) has changed because of my queries...Info on that will be useful as well.

Yoni Keren
  • 1,032
  • 2
  • 11
  • 24
  • You may want to take a look at https://stackoverflow.com/questions/784173/what-are-the-performance-characteristics-of-sqlite-with-very-large-database-file although in and of itself a single table of 350k entries is what I would consider "small". You may also want to investigate the [VACUUM command](https://sqlite.org/lang_vacuum.html) but read that page entirely and be sure you understand what it's doing. – Bacon Bits Oct 15 '17 at 22:17
  • Show the database schema and the slow queries. – CL. Oct 16 '17 at 07:43
  • @CL I cannot do that fully but that's as much info as I can disclose: There's a table Subjects with some columns (most of them are defined as text [is varchar any better?!]),one primary key (int), no foreign keys or constraints other than the primary key, and the slow query is Select count(*) from subjects; – Yoni Keren Oct 17 '17 at 09:58
  • @'Bacon Bits' Is 350K small in terms of SQLite as well? If so, I'm a bit confused- it takes a long time (>15 secs) in my shell to query for the number of elements in my table (literally i just type sqlite3 mydb.db in the shell and count(*) from some_table; and that takes a long long time). I'm asking because I want to add about 20 million more to that table, and I don't want to wait for 15 mins for it to respond!. – Yoni Keren Oct 17 '17 at 10:01

0 Answers0