Skip to content

SQLite browser lags #761

Closed
Closed

Description

@abhilash1in

I have a database of size 90 MB. SQLite browser doesn't seem to handle it too well. It has a huge lag while browsing or updating the db.

Activity

  1. vtronko commented on Sep 2, 2016

    @vtronko
    Member

    Define "doesn't seem to handle it to well". What's exactly happening? UI freeze?

  2. chrisjlocke commented on Sep 2, 2016

    @chrisjlocke
    Member

    Is it a locally attached database, or on a network?
    Although only 90 MB, how many tables and rows within those tables are there?
    Is it just when you change tabs? You said 'updating the db' - can you give more detailed steps to reproduce?

    I created a table with four fields and filled it with 549,000 rows. The fields just had 'a' as a content. The only real lag I see is changing a field type, eg, from text to integer.
    Switching to the 'Browse Data' tab creates 5 SQL queries (2 of which are possibly superfluous, but may not be) or possibly more, if the connection has closed ... is this a point of delay for you?

  3. abhilash1in commented on Sep 2, 2016

    @abhilash1in
    Author

    It is a local database. I have just one table with 260 rows.

    My table create statement is as follows:

    CREATE TABLE "slides" ( 
    `slide_no` INTEGER NOT NULL,
    `slide_type` INTEGER NOT NULL, 
    `text_size` INTEGER NOT NULL,
    `heading` TEXT, 
    `sub_heading` TEXT, 
    `img` BLOB, 
    `string1` TEXT, `string2` TEXT, `string3` TEXT, `string4` TEXT, `string5` TEXT, 
    `read` INTEGER, 
     PRIMARY KEY(slide_no) );
    

    All the fields are small (less than hundred characters) except img (of type blob with approximately 33000 characters in every row) and string1 (of type text with approximately a 1000 characters in every row)

    By 'updating the db' I meant, adding new rows to the db or editing existing rows in the db.

    When I open SQLite Browser and open the 'Browse Data' tab from any other tab, UI freezes and it takes about 5 seconds to load. Once it loads, when I try to scroll (up/down or left/right), it lags, UI freezes for about 4-5 seconds again (sometimes it even says 'Not responding' for a while). When I click on any field to edit it, it takes quite some time to get selected.

  4. justinclift commented on Sep 2, 2016

    @justinclift
    Member

    @abhilash1in Just to check, which version are you using, and which OS is it running on?

    We made some performance enhancements with the recent 3.9.0, but if you're using that one and it's still not great it sounds like more effort is probably needed.

    As a second thought, if you're on Windows or OSX, would you be ok to try the latest nightly build? That has some internal changes which should make some operations faster, so it might help. 😄

  5. abhilash1in commented on Sep 2, 2016

    @abhilash1in
    Author

    I'm using 3.9.0 on Windows 10. I've faced the lag since version 3.8.0. It was infact why I updated to 3.9.0 recently.

    I shall try the latest nightly build soon and let you know!

  6. abhilash1in commented on Sep 2, 2016

    @abhilash1in
    Author

    I must mention that 3.9.0 has been significantly faster than 3.8.0! But still not fast enough

  7. chrisjlocke commented on Sep 2, 2016

    @chrisjlocke
    Member

    Any chance of a copy of the database? Only ask to test it on another machine. Such a lag sounds 'odd' so wanted to check it here...
    From the schema you've provided, I could knock up a 'possible duplicate', but just wanted to cut a corner or two! 😉

  8. abhilash1in commented on Sep 2, 2016

    @abhilash1in
    Author

    It's for a client and I can't share it publicly! Even I'm beginning to wonder now if my PC is starving for resources with Android Studio and Chrome browser open in the background! If you can email me, I might be able to share it personally! abhilash1in@gmail.com 😉

  9. justinclift commented on Sep 2, 2016

    @justinclift
    Member

    @chrisjlocke This one is yours for in-depth investigation (eg knock up the duplicate for testing, or email @abhilash1in, etc). 😄

    @abhilash1in As a thought, do you have the "Edit Database Cell" panel open? If so, see if turning that off makes a difference. If it doesn't something else to investigate is this:

    1. Make a copy of the database
    2. In the copy, empty out the image column. eg UPDATE slides SET img = NULL, and see if the performance noticeably changes
    3. Also try the same "empty out X column" with other ones too, just to test. It might also be useful to just empty out some of the images (use a WHERE clause), to see if there's a threshold where things change

    Those should at least be decent steps to gather useful info with. 😄

  10. abhilash1in commented on Sep 2, 2016

    @abhilash1in
    Author

    I emptied the img column in a copy of the database and browser got lightning fast!

    I cant seem to figure out a suitable WHERE clause for the img attribute to empty only some of the records. Assistance please?

  11. chrisjlocke commented on Sep 2, 2016

    @chrisjlocke
    Member

    Feel free to ZIP the database and email it to me at chris@chrisjlocke.co.uk

    I can knock up a program which wipes out blocks of X records (eg, 10 or 20) and see what happens.

  12. chrisjlocke commented on Sep 2, 2016

    @chrisjlocke
    Member

    I've just amended my database with about 30,000 characters in each of the four fields, and while there was no lag, DB4S did 'cough' and thus, showed no data.

    image

    Will try with BLOBS, as that seems to be the performance 'issue'.

  13. chrisjlocke commented on Sep 2, 2016

    @chrisjlocke
    Member

    Can confirm long strings in a field make DB4S cough. Have attached a small database. Shove 100,000 characters in a field, and DB4S struggles browsing data or viewing records via the 'Execute SQL' tab.

    It didn't make much difference having a long continuous line of text or text with spaces in it (thought the grid would prefer the latter). As the grid can only display X characters, would it be worth the grid only showing the first X characters of the field? Or would that have too much of a speed impact to be worth it?
    Creating a SQL query without the long field (so 'select rowid from a') is speedy. 'select * from a' is not. This would point to it being a 'displaying the data' issue rather than 'DB4S can't cope with large blobs of data' issue.

    The problem isn't twice as bad with 200,000 characters in each field, but it is worse. I just changed the database to 200,000 chars in each record, and its a lot slower. (bloat1b.zip)
    Obviously, if you're storing 300 KB pictures in a database (as per @abhilash1in) this would explain the even longer delays.

    Using select substr(a,1,40000) from a works, but only in the 'Execute SQL' tab - it would be handy if the 'browse data' tab could 'truncate' data, with the 'Edit Data Cell' pane obviously showing the full data.

    bloat1.zip
    bloat1b.zip

  14. justinclift commented on Sep 3, 2016

    @justinclift
    Member

    Interesting. As a test, it's probably worth checking whether monospaced fonts (eg Courier) make a difference.

    With the hex view in the Edit Cell panel, they're noticeably faster than non-monospaced fonts.

    That's unlikely to be the complete solution though. 😉

  15. 22 remaining items

  16. MKleusberg commented on Sep 6, 2016

    @MKleusberg
    Member

    @chrisjlocke I was confused by this, too 😃 For me at least the problem was that the minimum value for this field is 1000. If you set a value below that (I was trying 5 for testing purposes) Qt recognises it as an invalid value and changes it back to 5000. Other than that I couldn't see any problem with the settings dialog.

  17. MKleusberg commented on Sep 6, 2016

    @MKleusberg
    Member
    It appears to only display/retrieve the first 5,000 characters from the field. So the good news is that it displays in the grid much quicker, but the downside is that you can no longer view/edit the full field contents. Consider it a temporary tweak... :wink:
    

    This should be fixed by 4b79f92. So the next nightly build might be worth checking out 😃

  18. justinclift commented on Sep 6, 2016

    @justinclift
    Member

    That's awesome @MKleusberg. I was wondering how to distinguish between the two different uses for the same data() call.

    As you've just shown... that's what roles are for. 😄

  19. vtronko commented on Sep 6, 2016

    @vtronko
    Member

    @MKleusberg because I set minimum to 1k :)
    @chrisjlocke do you really think 5k symbols can be displayed handy? Editor is just for that, grid is preview stuff...

  20. chrisjlocke commented on Sep 6, 2016

    @chrisjlocke
    Member

    No, I don't think 5000 characters is handy ... The grid can only show at tops 20, and as its truncated, shouldn't be edited, so I was entering low values, and it was being reset to 5000. . I didn't realise the 'edit cell' pane was showing the full text either... It only displayed '5000 characters' in its status. I didn't test it any further or deeper as I thought it was work in progress...

    I assume that's what you meant by 'do you really think 5k symbols can be displayed handy?' - did you think I wanted more??

  21. vtronko commented on Sep 6, 2016

    @vtronko
    Member

    @chrisjlocke feel free to change it?

    And yeah, 1k is lower limit. As I said, feel free to change it, even via github. Say, to 1.

  22. justinclift commented on Sep 6, 2016

    @justinclift
    Member

    Yeah, 1 makes sense to me as a lower boundary. If people want to choose that, we might as well let them, it's shouldn't hurt anything. 😄

  23. MKleusberg commented on Jan 3, 2017

    @MKleusberg
    Member

    Any updates on this issue?

  24. justinclift commented on Jan 3, 2017

    @justinclift
    Member

    From memory of ages ago, I changed the lower limit to be 1 (as in a singular character).

    Haven't followed up though to check for performance differences. 😉

  25. MKleusberg commented on Jan 3, 2017

    @MKleusberg
    Member

    You're right, looks like the minimum is 1 now. But I'm not sure if that helps performance-wise either 😉

  26. sky5walk commented on Dec 29, 2018

    @sky5walk

    I briefly compared the other browsers mentioned and they most likely use owner drawn gadgets. They are swifter but not as appealing, feature rich, or available as 64-bit app. The gui's become cluttered quickly, stuffing too much in view. It is a worthwhile comparison to improve threaded actions in DB4S.

  27. chrisjlocke commented on Jan 8, 2019

    @chrisjlocke
    Member

    I'll close this, as this seems a lot better in the latest nightly.
    @abhilash1in - I know its been over 28 months since you raised this, but if you still use DB4S and want to have a go with the latest version, hopefully you'll see a vast improvement.

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

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions