Duplicate queries in sql application log on Browse data

[mglatz]

When I go do browse data tab, display SQL log submitted by application, clear that log and select any table/view, I see that both the queries (count and select) gets executed twice or more times producing 4 or more rows in sql log instead of two rows.
On another database I see that the sql log produces 70(!) queries just for selecting a table (not a view) that has no filter or sorting.

I'm opening this issue because:

• DB4S is crashing
• DB4S has a bug
• DB4S needs a feature
• DB4S has another problem

I'm using DB4S on:

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

I'm using DB4S version:

• 3.10.0
• 3.10.0-beta*
• 3.9.1
• Other: ___

I have also:

• Tried out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds
• Searched for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20

[justinclift]

Oh wow, 70 queries sounds pretty excessive. With that 70 generated queries, was there any kind of scrolling down the browse data tab? Asking because (in theory) DB4S is supposed to load a small amount of rows when going to the Browse Data tab, and only then load more rows as the window is scrolled down. eg load rows on demand for display

It sounds like something is going wrong with that, but figured I'd ask just in case. 😄

Also, are we able to get hold of the database schema? Whatever is causing the problem here is probably something to do with how DB4S is understanding the schema, so it'd be useful for @MKleusberg to be able to reproduce the bug on his local development machine. He's on vacation for a few more days though, so none of this can really get looked at until then. 👼

[mglatz]

On any db and any table I open I get 4 SQL statements (I would expect two). I only get more than 4 rows (up to 70) when the db is opened as project (sqbpro).

I've been able to reproduce it:

1. create new db, save as sample.db3
2. CREATE TABLE test ( id INTEGER, Field2 INTEGER, Field3 INTEGER, PRIMARY KEY(id) )

viewing table produces
SELECT COUNT() FROM (SELECT _rowid_, FROM test ORDER BY _rowid_ ASC);
SELECT _rowid_,* FROM test ORDER BY _rowid_ ASC LIMIT 0, 50000;
SELECT COUNT() FROM (SELECT _rowid_, FROM test ORDER BY _rowid_ ASC);
SELECT _rowid_,* FROM test ORDER BY _rowid_ ASC LIMIT 0, 50000;

3. save as project as sample.db3.sqbpro
4. execute insert into test values(null,1,1)

viewing table produces
SELECT COUNT() FROM (SELECT _rowid_, FROM test ORDER BY _rowid_ ASC);
SELECT _rowid_,* FROM test ORDER BY _rowid_ ASC LIMIT 0, 50000;
SELECT COUNT() FROM (SELECT _rowid_, FROM test ORDER BY _rowid_ ASC);
SELECT _rowid_,* FROM test ORDER BY _rowid_ ASC LIMIT 0, 50000;
SELECT COUNT() FROM (SELECT _rowid_, FROM test ORDER BY _rowid_ ASC);
SELECT _rowid_,* FROM test ORDER BY _rowid_ ASC LIMIT 0, 50000;
SELECT COUNT() FROM (SELECT _rowid_, FROM test ORDER BY _rowid_ ASC);
SELECT _rowid_,* FROM test ORDER BY _rowid_ ASC LIMIT 0, 50000;
SELECT COUNT() FROM (SELECT _rowid_, FROM test ORDER BY _rowid_ ASC);
SELECT _rowid_,* FROM test ORDER BY _rowid_ ASC LIMIT 0, 50000;
SELECT COUNT() FROM (SELECT _rowid_, FROM test ORDER BY _rowid_ ASC);
SELECT _rowid_,* FROM test ORDER BY _rowid_ ASC LIMIT 0, 50000;

here is my db and project
sample.zip

[justinclift]

Thanks @mglatz, that should help us get it figured out. 😄

[chrisjlocke]

See also #1007 and #641. This started getting looked into, but after Martin added debug code, I couldn't 'read it' in Windows, as I couldn't run it in 'console' mode.
Theoretically, I should be able to run it within VS (which is how you did it last time, Justin - yes you did ... I saw the screenshot.... ;) ) which should help (when Martin gets back and bursts the blisters on his feet...)

[justinclift]

Heh Heh Heh

That was ages ago, so yeah... I have nearly no recollection. 😉

[chrisjlocke]

Is there a step 3a, "Close database and open project" ?

I've tried, but can't reproduce this. (Win 10x64, using v3.10) (Duplicate queries, yes, but not 70 queries)
https://screencast-o-matic.com/watch/cbjUQcIcXL

[mglatz]

yes there is. I also saved the db after each change

[MKleusberg]

@mglatz I've just pushed a commit which in theory should reduce the query count from 4 to 2. Are you ok to download tomorrow's nightly build and test if it's working for you? 😄

I've also looked at your project file but wasn't able to reproduce the 70 queries. Can you check if that's still happening for you? If so, it would also be interesting to know which queries you get (always the same ones or different ones).

[mglatz]

hi, seems the issue is fixed.
before installing nightly I tested it once more and I still got the issue. I got 4 rows for each table when I opened .db3 file directly, I got 4 - 70 rows for each table when I opened the same table with .sqbpro project that has about 20 saved queries.
After installing latest nightly I get only two rows (count and select) for each table on .db3 and .sqbpro
Thanks for fixing, this makes browsing considerably faster 👍

[justinclift]

Excellent. 😄

As a data point, @MKleusberg also recently adjusted (last few days) how data is fetched from the database. Instead of using a single thread, it's now multithreaded, to better co-ordinate and (seems like almost) prefetch data. That's probably contributing to the speed up as well.

That's new code though, so @MKleusberg is definitely very interested in any weirdness that shows up which might be an intended side effect to fix. eg keep a look out 😀