4

I have a database file with 21.5 GB and I am trying to do some queries on the data but it is not responsive. It is stuck in processing the query without any result eventually. Even a simple query to count the number of rows in the table doesn't work.

When limiting the query to a small number it work fine. Something like this

select * from Table limit 30.

The problem is when trying perform a query on the whole table. It gets stuck. I am wondering if there is any optimization I can do so I can query the whole data set.

Anybody knows a better way to query such a large file?

Thank you in advance.

jelmood jasser
  • 800
  • 1
  • 12
  • 30
  • "Some queries" is not really something we can help with, but if you can be more specific and show us what you're trying to do, and where you think the problem occurs, at least we have something to work with. – tadman May 05 '18 at 18:01
  • @tadman Actually no query works. Even just a query to count the number of rows inside the table doesn't work. – jelmood jasser May 05 '18 at 18:03
  • Are you sure your database checks out alright and isn't corrupted for some reason? I'd test it with other SQLite tools. If it's broken, make a copy and try and recover it. – tadman May 05 '18 at 18:04
  • @tadman The database is fine. It works with limited queries. – jelmood jasser May 05 '18 at 18:12
  • It doesn't sound "fine" if you can't do a simple count. I think you have a corruption issue, but it might only impact a subset of the tables. Is it possible to rebuild the `.sqlite` file? – tadman May 05 '18 at 18:14
  • In general, this is caused by the lack of useful indexes. Show the database schema and the actual queries you want to speed up. – CL. May 05 '18 at 19:52
  • @jelmoodjasser Please give example of a simple query that is slow except for count. – Daniele Testa May 25 '21 at 08:36

0 Answers0