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
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?
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.
@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. 😄
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! 😉
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 😉
@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:
Make a copy of the database
In the copy, empty out the image column. eg UPDATE slides SET img = NULL, and see if the performance noticeably changes
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. 😄
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.
Will try with BLOBS, as that seems to be the performance 'issue'.
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.
@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.
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 😃
@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...
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??
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.
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.
Activity
vtronko commentedon Sep 2, 2016
Define "doesn't seem to handle it to well". What's exactly happening? UI freeze?
chrisjlocke commentedon Sep 2, 2016
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?
abhilash1in commentedon Sep 2, 2016
It is a local database. I have just one table with 260 rows.
My table create statement is as follows:
All the fields are small (less than hundred characters) except
img(of typeblobwith approximately 33000 characters in every row) andstring1(of typetextwith 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.
justinclift commentedon Sep 2, 2016
@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. 😄
abhilash1in commentedon Sep 2, 2016
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!
abhilash1in commentedon Sep 2, 2016
I must mention that 3.9.0 has been significantly faster than 3.8.0! But still not fast enough
chrisjlocke commentedon Sep 2, 2016
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! 😉
abhilash1in commentedon Sep 2, 2016
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 😉
justinclift commentedon Sep 2, 2016
@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:
UPDATE slides SET img = NULL, and see if the performance noticeably changesThose should at least be decent steps to gather useful info with. 😄
abhilash1in commentedon Sep 2, 2016
I emptied the
imgcolumn in a copy of the database and browser got lightning fast!I cant seem to figure out a suitable
WHEREclause for theimgattribute to empty only some of the records. Assistance please?chrisjlocke commentedon Sep 2, 2016
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.
chrisjlocke commentedon Sep 2, 2016
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.
Will try with BLOBS, as that seems to be the performance 'issue'.
chrisjlocke commentedon Sep 2, 2016
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 aworks, 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
justinclift commentedon Sep 3, 2016
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. 😉
22 remaining items
MKleusberg commentedon Sep 6, 2016
@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.
MKleusberg commentedon Sep 6, 2016
This should be fixed by 4b79f92. So the next nightly build might be worth checking out 😃
justinclift commentedon Sep 6, 2016
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. 😄
vtronko commentedon Sep 6, 2016
@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...
chrisjlocke commentedon Sep 6, 2016
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??
vtronko commentedon Sep 6, 2016
@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.
justinclift commentedon Sep 6, 2016
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. 😄
MKleusberg commentedon Jan 3, 2017
Any updates on this issue?
justinclift commentedon Jan 3, 2017
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. 😉
MKleusberg commentedon Jan 3, 2017
You're right, looks like the minimum is 1 now. But I'm not sure if that helps performance-wise either 😉
sky5walk commentedon Dec 29, 2018
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.
chrisjlocke commentedon Jan 8, 2019
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.