0

I have a table holding data about entities. When I had 250,000 entities sqlite3 worked reasonably fast, but after adding 100k more (now 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? I intend to add a few million entities to the table.

Now that I've made some queries, it is reasonably fast again, even after closing sqlite3 and the shell. I'm not sure if it's because there's something loaded to my RAM, hard drive's cache or SQLite's own database internal representation (database file) changed because of my queries.

user4157124
  • 2,809
  • 13
  • 27
  • 42
Yoni Keren
  • 1,170
  • 2
  • 13
  • 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
  • 1
    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