sqlitebrowser
sqlitebrowser copied to clipboard
sqlitebrowser
More of a question
I have a query
SELECT "words"."word", count("files"."file") as "count" FROM "words", "phones", "files" WHERE "words"."word_id" = "phones"."word_id" AND "words"."word_id" = "files"."word_id" AND "phones"."phone1" = 'SH' GROUP BY "words"."word"
What is confusing me is if I run through ODBC and libreoffice base the above returns records and also fairly quickly.
If I open the database direct with sqlitebrowser on ubuntu 22.04 with a db created in python I get the following
Execution finished without errors.
Result: 0 rows returned in 24529ms
At line 1:
SELECT "words"."word", count("files"."file") as "count" FROM "words", "phones", "files" WHERE "words"."word_id" = "phones"."word_id" AND "words"."word_id" = "files"."word_id" AND "phones"."phone1" = 'SH' GROUP BY "words"."word"
1stly as seen its very slow but also how can it return zero rows when the exact same via ODBC does. Maybe its the snap of sqlitebrowser on Ubuntu 22.04 as never had anything like this on 20.04
Yeah its the snap on Ubuntu 22.04 as just removed and reinstalled via apt
Execution finished without errors.
Result: 434 rows returned in 16927ms
At line 1:
SELECT "words"."word", count("files"."file") as "count"
FROM "words", "phones", "files" WHERE "words"."word_id" = "phones"."word_id" AND "words"."word_id" = "files"."word_id" AND
"phones"."phone1" = 'SH' GROUP BY "words"."word"
Dunno why the snap fails as you can browse the tables, still think its much slower than ODBC but hey.
Yeah, that seems weird. As an aside the 22.04 release of Ubuntu seems like a bug fest that shouldn't have been released in it's current state.
There are significant issues with it in many areas, several that I'm astounded made it through testing. eg People trying to install multiple input and display languages (Chinese, Japanese, Korean, English - all together) weren't able to, nor being able to receive email with some SMTPD servers due to incompatibility with the in-built new OpenSSL version, etc. :frowning:
What version of db4s was you using originally, compared to what you've reinstalled? The database obviously contains 'phones', which is a phone number? If you 'update' that with zeros or something, could you send me the database? I'd be interested in testing it further and while I'd need the same number of rows as your database, the data itself isn't needed.
Its phonetic database based on the ml-commons dataset but I will do a git push tonight as the full db is in a repo as its public data is here https://github.com/StuartIanNaylor/ProjectEars/blob/main/dataset/ml-commons/ml-commons.tar.xz its just compressed there. Still needs push as don't think that version has the query used but give us an hour or so.
It must be the snap as remove the snap and install the non snap version from the repo and it works and also does librebase via odbc.
PS guys the gui design editor in librebase is such a handy tool as I always forget much SQL and would be lost without it until checking the sql results starts jogging my memory again. Often I am building up SQL there and the copy and paste but its a complete crock for any import or export of data which sqlitebrowser is great for.
Anyway 13.12.1 on sqlite 3.33.0 is the repo version not sure what the snap version was.
The snap so it says is https://github.com/deepsidhu1313/sqlitebrowser-snap-nightly 13.12.2 without reinstalling
[edit] @chrisjlocke https://github.com/StuartIanNaylor/ProjectEars/blob/main/dataset/ml-commons/ml-commons.tar.xz
Anyway 13.12.1 on sqlite 3.33.0
I'm guessing that's 3.12.1, which is one rev out - 3.12.2 is the last official version. Even then, that's out of date, as the nightly is improved on that. Thanks for confirming. Thanks too for the link to the database.
Do you have any numbers for the time your query takes via ODBC? Maybe this gives us a clue where to start looking for the issue.
A quick analysis on my system shows that something like 99% of the CPU time is spent in fetching one table row after the other, a 435 times in total (one for each row and one extra for figuring out that there is no more data). So one explanation would be that when fetching via ODBC only the first 50 or so records are loaded initially.
The execution time is problematic but the strange problem with the snap version is it produces no results.
Execution finished without errors.
Result: 0 rows returned in 24529ms
At line 1:
SELECT "words"."word", count("files"."file") as "count" FROM "words", "phones", "files" WHERE "words"."word_id" = "phones"."word_id" AND "words"."word_id" = "files"."word_id" AND "phones"."phone1" = 'SH' GROUP BY "words"."word"
Whilst be simply uninstalling the snap and installing the non snap repo version
Execution finished without errors.
Result: 434 rows returned in 16927ms
At line 1:
SELECT "words"."word", count("files"."file") as "count"
FROM "words", "phones", "files" WHERE "words"."word_id" = "phones"."word_id" AND "words"."word_id" = "files"."word_id" AND
"phones"."phone1" = 'SH' GROUP BY "words"."word"
The query did return results, its that it is noticeable that ODBC seems faster and when the result comes in you can select the last record immediately as seems not to be paging. To actual speed might just be observer bias and maybe no different but not sure how I could time that guess I could install https://pypi.org/project/pyodbc/ and try. That really wasn't the problem though it the strange occurrence of the snap on ubuntu 22.04 returning zero results. Is 24529ms past the default timeout?
the strange occurrence of the snap on ubuntu 22.04 returning zero results
I've tried 3.12.0 and 3.12.1 (albeit on Windows) and get 434 rows returned. 3.12.1 returns the results in slightly quicker time. 3.12.2 returned the results slower than 3.12.0. But my machine is wacky. Either way, I couldn't duplicate the 'no rows returned' issue.
As said its the snap version on ubuntu that does not repo versions work.
Yes - I was just making sure it wasn't an issue in the earlier versions of DB4S.