3

On my database file of 21.5 GB even a simple query to count the number of rows doesn't work. When limiting the query to a small number it works fine:

select * from Table limit 30

When trying a query on the whole table it gets stuck. Is there is any optimization I can do so I can query the whole data set?

user4157124
  • 2,809
  • 13
  • 27
  • 42
jelmood jasser
  • 878
  • 1
  • 13
  • 30
  • 1
    "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
  • 1
    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
  • 1
    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
  • 1
    @jelmoodjasser Please give example of a simple query that is slow except for count. – Daniele Testa May 25 '21 at 08:36
  • Not using a WHERE clause requires that all rows be returned, and SELECT * requires all columns to be returned. For a DB that big, that's a gigantic amount of data that has to be returned, requiring a lot of processing time and a huge amount of memory. You should limit the amount of data your query returns by specifying only the columns that you need and using a WHERE clause to reduce the number of rows. If you want all rows and columns, you're stuck with horrific performance. Buy a good SQL book or find a good tutorial. – Ken White Oct 12 '22 at 23:39
  • 1
    It's unclear why you would need to return all rows and columns at all. No user is going to be able to scroll through that much data. Also, SQLite is a light-purpose database. If you're dealing with that much data, you should consider moving to a full RDBMS instead. – Ken White Oct 12 '22 at 23:42

0 Answers0