Skip to content

Avoid unnecessary queries when populating filtered table (workaround for invalid rows) - #1305

Closed
mgrojo wants to merge 1 commit into
masterfrom
invalid_rows_filter
Closed

mgrojo wants to merge 1 commit into
masterfrom
invalid_rows_filter

Conversation

@mgrojo

@mgrojo mgrojo commented Jan 23, 2018

Copy link
Copy Markdown
Member

When a table is filtered and the browsed tabled is changed and then we
return to this filtered table, the table view shows several incorrect
disabled rows. The same may happen when refreshing this table. This seems
to be produced by a (yet unsolved) concurrency problem triggered by
several consecutive queries, that are signalled by setting the text of
the stored filters in the table header filter.

These unnecessary queries are avoided by setting the stored filter values
at the same time as the table in the SQL table model, making sure that
no intermediate queries are executed while the filters are set.

This seems to work around the concurrency problem and the symptom of the
invalid rows does not appear in those situations.

These are the queries that I get before the fix:

SELECT COUNT(*) FROM (SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` ORDER BY `_rowid_` ASC);
SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` ORDER BY `_rowid_` ASC LIMIT 0, 300;
SELECT COUNT(*) FROM (SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` WHERE `Category` = 'Medical'  ORDER BY `_rowid_` ASC);
SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` WHERE `Category` = 'Medical'  ORDER BY `_rowid_` ASC LIMIT 0, 300;
SELECT COUNT(*) FROM (SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` WHERE `Category` = 'Medical'  AND `Item` = 'Syringes'  ORDER BY `_rowid_` ASC);
SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` WHERE `Category` = 'Medical'  AND `Item` = 'Syringes'  ORDER BY `_rowid_` ASC LIMIT 0, 300;

After the fix:

SELECT COUNT(*) FROM (SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` WHERE `Category` = 'Medical'  AND `Item` = 'Syringes'  ORDER BY `_rowid_` ASC);
SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` WHERE `Category` = 'Medical'  AND `Item` = 'Syringes'  ORDER BY `_rowid_` ASC LIMIT 0, 300;

But I'm still able to reproduce the invalid rows problem. This is what happens when you open a project with saved filters for a table:

SELECT COUNT(*) FROM (SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` ORDER BY `_rowid_` ASC);
SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` ORDER BY `_rowid_` ASC LIMIT 0, 300;
SELECT COUNT(*) FROM (SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` WHERE `Category` = 'Medical'  AND `Item` = 'Syringes'  ORDER BY `_rowid_` ASC);
SELECT `_rowid_`,* FROM `main`.`100m2012Autumn` WHERE `Category` = 'Medical'  AND `Item` = 'Syringes'  ORDER BY `_rowid_` ASC LIMIT 0, 300;

Since there are two consecutive count queries, the problem is reproduced. And in that case the filter values in the table header are not displayed, as I already reported in #1222.

I hope this helps to find the concurrency problem but, in any case, removing unnecessary queries is good. But it should be carefully reviewed, since I'm not sure about possible side effects of the solution.

(The tests are made with this DB: https://db4s-beta.dbhub.io/nicva/Marine%20Litter%20Survey%20(Keep%20Northern%20Ireland%20Beautiful).sqlite)

When a table is filtered and the browsed tabled is changed and then we
return to this filtered table, the table view shows several incorrect
disabled rows. The same may happen when refreshing this table. This seems
to be produced by a (yet unsolved) concurrency problem triggered by
several consecutive queries, that are signalled by setting the text of
the stored filters in the table header filter.

These unnecessary queries are avoided by setting the stored filter values
at the same time as the table in the SQL table model, making sure that
no intermediate queries are executed while the filters are set.

This seems to work around the concurrency problem and the symptom of the
invalid rows does not appear in those situations.
@mgrojo
mgrojo requested a review from MKleusberg January 23, 2018 23:23
@justinclift justinclift added the enhancement Feature requests. label Jan 24, 2018
@MKleusberg

Copy link
Copy Markdown
Member

Yes, this would fix the issue. The remaining problem with the project files can probably be solved in a similar manner.

I'm still not really sure though about this commit. I think the best way to solve this issue is to fix the underlying problem. In my opinion this is the interface of the SqliteTableModel class. We had various issues over the last years which were all solved by that interface, so I think we need to fix the issue right there. Some time ago I have created issue #1247 for this.

The basic idea is to change the SqliteTableModel class to offer a number of functions for changing the select query (setting filter, sort order, encoding, ...) but don't apply them immediately like now. Currently, changing any settings applies them immediately, except for some which we don't apply immediately to avoid 2 or 4 queries instead of one. But that all happens in a very convoluted way which is frankly impossible to understand.

So what I'm saying is this: let's fix it in the SqliteTableModel class, but if we don't manage to do this before the release let's apply your PR and fix SqliteTableModel afterwards 😄

@mgrojo

mgrojo commented Jan 27, 2018

Copy link
Copy Markdown
Member Author

@MKleusberg I agree with your analysis and proposal.

Maybe we need something like what Qt provides for signals (blockSignals): by default all these functions that modify the query apply them immediately but when we have a series of query modifications in a given context, we can call oldState = model->automaticQueryExecution(false) and when all the changes are done, we restore the previous state with model->automaticQueryExecution(oldState). That would be in my opinion an easy improvement over the current situation, but maybe you are thinking on something more radical and rethinking and improving the whole interface.

@mgrojo

mgrojo commented May 22, 2018

Copy link
Copy Markdown
Member Author

The visible issues are gone with #1394, so I was thinking that this workaround could be discarded now. But while testing that PR, I've seen, that the 4 queries (count and rows without filters, and then count and rows with filters) are still performed in the scenario I described here. Are they cancelled without effect in performance or would this still be useful for improving that aspect? I finally leave this open for considering this question.

@MKleusberg

Copy link
Copy Markdown
Member

Since we're getting closer to the 3.11 release and won't rewrite the SqliteTableModel interface until then I'm going to merge this fix here. It's still working very well 👍

@MKleusberg MKleusberg closed this Oct 3, 2018
@MKleusberg
MKleusberg deleted the invalid_rows_filter branch October 3, 2018 13:45
@mgrojo
mgrojo restored the invalid_rows_filter branch October 14, 2023 21:27
@mgrojo
mgrojo deleted the invalid_rows_filter branch October 14, 2023 21:47
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

enhancement Feature requests.

Projects

None yet

Development

Successfully merging this pull request may close these issues.

3 participants