Skip to content

How to increase performance? #1364

Closed
Closed

Description

@l-g

Details for the issue

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!

I'm using DB4S on:

  • Windows: ( version: ___ )
  • Linux: ( distro: ___ )
  • Mac OS: ( version: ___ )
  • Other: ___

I'm using DB4S version:

  • 3.10.1
  • 3.10.0
  • 3.9.1
  • Other: ___

I have also:

Activity

  1. chrisjlocke commented on Apr 12, 2018

    @chrisjlocke
    Member

    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.

    image

    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.

  2. justinclift commented on May 26, 2018

    @justinclift
    Member

    @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. 😄

  3. mgrojo commented on Oct 8, 2018

    @mgrojo
    Member

    @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.

  4. l-g commented on Oct 8, 2018

    @l-g
    Author

    Yes, but the need is no longer there. Thanks anyway.

  5. justinclift commented on Oct 9, 2018

    @justinclift
    Member

    No worries @l-g. 😄

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions