You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I have a DB (basically an Excel sheet that is too big for Excel) consisting of one table with ten columns, all containing simple datatypes such as text, integers and dates. All in all this table has between 3 and 4 millions of rows.
Working in the GUI (scrolling, sorting a column, changing tabs and similar operations) with this DB on laptop with an eight core i7 and 16 GB memory is pretty slow. One CPU core is at 100 % but sqlitebrowser does not seem to utilise more than one core. On the other hand, I don't think CPU is the bottleneck here, rather it is memory: sqlitebrowser only uses about 1.5 GB of RAM when working with this database. The actual file is 450 MB. "Only" consuming 1.5 GB makes me think sqlitebrowser is not loading the whole dataset into memory.
Is it possible to change some settings that will make the program faster under these circumstances?
I think there should be an article about improving performance in the Wiki!
First off, its always good to run the latest version. While there isn't much of a difference between 3.10 and 3.10.1, there is a difference between 3.10 and our latest nightly build version. While the nightlies contain minor issues and glitches (they are nightly builds after all) they also contain numerous fixes. The SQLite bundled with them has also been updated, which brings benefits and minor bug fixes.
The nightlies contain multi-threaded improvements, which could aid performance in your scenario.
I'd also look at the 'prefetch block size' option in Preferences.
This is how many records is loaded into the grid at any one time. Try setting this higher/lower, depending on your needs.
A database with 4 million rows is always going to have 'issues', unless you use specific 'select' statements to only view the records you need, not every single record. Do you have to scroll through 4 million records? If you're only after a specific date range, this can be set in the 'select' statement, in the 'Execute SQL' tab.
@l-g Did you had some time to try the new version? The improvements are already in the nightly builds. We'd like to close this issue as clean up before the release of our next stable version.
Activity
chrisjlocke commentedon Apr 12, 2018
First off, its always good to run the latest version. While there isn't much of a difference between 3.10 and 3.10.1, there is a difference between 3.10 and our latest nightly build version. While the nightlies contain minor issues and glitches (they are nightly builds after all) they also contain numerous fixes. The SQLite bundled with them has also been updated, which brings benefits and minor bug fixes.
The nightlies contain multi-threaded improvements, which could aid performance in your scenario.
I'd also look at the 'prefetch block size' option in Preferences.
This is how many records is loaded into the grid at any one time. Try setting this higher/lower, depending on your needs.
A database with 4 million rows is always going to have 'issues', unless you use specific 'select' statements to only view the records you need, not every single record. Do you have to scroll through 4 million records? If you're only after a specific date range, this can be set in the 'select' statement, in the 'Execute SQL' tab.
justinclift commentedon May 26, 2018
@l-g Are you still interested in faster performance with this?
Asking because we've being working on the performance side of things recently (in #1394), and have an initial build ready for people to test:
In my testing so far with a ~600MB database, the lag is now only about 1 second. That's heaps better than any of our releases so far. 😄
mgrojo commentedon Oct 8, 2018
@l-g Did you had some time to try the new version? The improvements are already in the nightly builds. We'd like to close this issue as clean up before the release of our next stable version.
l-g commentedon Oct 8, 2018
Yes, but the need is no longer there. Thanks anyway.
justinclift commentedon Oct 9, 2018
No worries @l-g. 😄