Repository navigation
Slow running Recursive CTE #409
Description
Sqlitebrowser performs very slow when running Recursive CTE. I have a table with >500K rows and it takes Sqlitebrowser about 6 minutes to finish the query. Other sqlite browsers/managers such as sqlitespy, database.net, etc take around 1 min and 45 sec to complete. The fastest is sqlitespeed. It takes only about 1 min and 15 sec to complete the query. Can somebody take a look at this?
Thanks
Activity
justinclift commentedon Aug 28, 2015
Are you able to share the database and slow query with us? Emailing it to me directly (justin@postgresql.org) is workable, if that helps. 😄
justinclift commentedon Aug 28, 2015
Received it. Have now forwarded it onto @MKleusberg (CC-ing yourself), in the hope he has time to investigate. 😄
sprintel commentedon Oct 29, 2015
Justin -
Any update? Did he get a chance to investigate?
Thanks,
AJ
MKleusberg commentedon Feb 17, 2017
Thanks for reminding me @sprintel and sorry for the delay! The main problem here is that we have to execute this query essentially twice. One time for getting the total row count like this:
And one time for actually getting the first block of data:
This explains why it's taking so much longer. The issue here is that we need to know the total row count before doing anything else to get the scrolling in the result view right. But this doesn't explain why it's taking 6 minutes for you. It should really "just" be 3 and a bit.
So out of curiosity, how long does it take on your system using the latest nightly build? Any improvement yet? If not, maybe we can provide you with a build that outputs some more debug information in order to find out where the extra time is coming from.
Another question would be if it might be a good idea to add a new option to the preferences dialog for disabling the
COUNT(*)thing. The cost of this would be less information when browsing and very jumpy scrolling.justinclift commentedon Feb 17, 2017
@MKleusberg As a thought, if we add debugging output to the build maybe we should leave it in and have it toggled on/off by a command line switch or something?
sprintel commentedon Feb 17, 2017
@MKleusberg - I recently got a new PC so I have different results when I tested the sample that I sent you. The latest DB browser (v3.9.1) now can complete the query in 149 second (2.5 minute) vs 68 second if I use sqlitespy. With my old PC, DB browser took 6 minutes to finish the query vs 1 min and 45 with sqlitespy. It seems DB browser processes the query faster but still lags behind other sqlite browsers.
I'm more than happy to help you run the query using the special build to figure out the issue. Just let me know.
justinclift commentedon Feb 17, 2017
@MKleusberg As an "out there" idea, remember how we add a Qt regex function upon startup? Don't suppose the presence of that might affect the communication path or something?
MKleusberg commentedon Mar 16, 2017
@justinclift Nah, that only gets called when you're actually performing a regex operation, i.e. not when simply browsing a table. The problem seems to be that we're essentially performing each query twice.
mgrojo commentedon Oct 4, 2018
@MKleusberg, might this be better with the current build?
MKleusberg commentedon Oct 15, 2018
@mgrojo Unfortunately not. I vaguely remember some changes I did a long time ago to slightly improve the performance for the Execute SQL tab but other than that nothing really changed. Even though we now have the multithreaded loading of data in place, the code for the Execute SQL tab doesn't profit from it. That's also why #1563 doesn't work for the Execute SQL tab - it would work, but because the main thread is stopped until the loading is done, you can't click the button. I am however working on this right now. In two days or so I hope to present a first prototype 😄
MKleusberg commentedon Oct 18, 2018
@sprintel Are you still interested in this issue? I'm asking because we are currently preparing some changes which might improve this. I'm still not sure how to further improve the performance in any noticeable way, but I think the version we have prepared is 1) again a bit faster and 2) has a cancel button. Do you want to give it a try? You can find the Windows and OSX build here:
sprintel commentedon Oct 18, 2018
I actually stop using this software because of the slow speed. I would like to test it but I'm wondering if you have a portable version that I can use. I'm allowed to use this software (it's approved) in my company but to install a program, it's such a hassle to go through the bureaucracy.
sprintel commentedon Oct 18, 2018
Never mind about the portable request. I'm able to extract the files from the installer and run it. I'll let you know.
justinclift commentedon Oct 18, 2018
We're already getting reports of a bunch of bugs (in #1575) with these v1 and v2 builds. Probably not good to use them on production data. 😉
sprintel commentedon Oct 18, 2018
I just tested it.
Other software: 222 Sec completely loaded
v.3.5.1 691 Sec completely loaded
v 3.11.999:
-185 Sec but not loaded at all (1st trial).
-253 Sec but partially loaded (2nd trial).
-Completely loaded took 345 Sec (see attached pic)
sprintel commentedon Oct 18, 2018
I just reran my other software and I took only 102 Sec this time. I also ran dbeaver and it took 90 sec.
justinclift commentedon Oct 18, 2018
Thanks @sprintel, that's good info. 😄
MKleusberg commentedon Jan 11, 2021
Are you still around, @sprintel? It took a long time but the current nightly build of DB4S is finally running your query at full speed. If you want to you can give it a try and tell us if it's working as expected. Or we can just close this issue as it's pretty old.
sprintel commentedon Jan 11, 2021
I’m still here but I already moved to a different job and don’t use sqlite anymore. I will ask somebody from my old team to test if they have time.
justinclift commentedon Jan 13, 2021
Thanks @sprintel, that'd be great! 😄
sprintel commentedon Jan 13, 2021
Unfortunately my old team already switched to a different sqlite browser. Since nobody will be testing it, I’m going to go ahead and close the ticket.
justinclift commentedon Jan 13, 2021
No worries, was a thought. 😄