[Interest] Interest Digest, Vol 91, Issue 26

[Roland Hughes]

On 4/18/2019 12:07 PM, Giuseppe D'Angelo wrote:
> On 18/04/2019 16:36, Roland Hughes wrote:
>> The "filter" for SQL is the WHERE clause on the SELECT statement. A 
>> "filter" in the C++ world works on the result of the query. Worst 
>> case it doubles the memory and transfer resources required. When the 
>> goal is reduction of required resources, a filter after the fact 
>> cannot help.
>
> The original statement said "Its not possible to make the filter part 
> of the SQL query" (sic). I asked why. This is not an answer, just a 
> show-off that you know how SQL filtering works.
>
It actually is the answer. A filter cannot reduce the amount of data in 
the query or the amount of data which must be transferred between the 
database and the application. And for the record.

======

I have a source model, which is QSqlModel based, and a filter proxy model.

Its not possible to make the filter part of the SQL query.. been down that road...

The problem is, since the QSqlModel is quite large, we cant do a "keep fetching till you can no longer fetch" as it would take almost 30 seconds to load the whole DB.

But, if the filter, doesn't find something in the first "batch" there is no way to call the next fetch (which is usually triggered by the scrollbar

Any thoughts?
======

They were trying to reduce the load time which means they have to put 
the filter requirements into the WHERE clause of the query. Instead of

SELECT * FROM some_table;

they have to be able to inject

WHERE columnA = "blah" AND columnB < "otherBlah:

That is the solution they need. Barring that the "batch" fetch logic has 
to be altered to fetch a valid batch, only counting rows which meet the 
selection logic.

Eventually the OP will get to the point they use their own model which 
holds up to 3 sets of "valid" records. A separate thread will 
dynamically build the SELECT statement including a WHERE clause with as 
much of the filter logic as possible. It will also have to have an 
escape hatch for pulling stubs and recovering from blanks.


-- 
Roland Hughes, President
Logikal Solutions
(630) 205-1593

http://www.theminimumyouneedtoknow.com
http://www.infiniteexposure.net
http://www.johnsmith-book.com
http://www.logikalblog.com
http://www.interestingauthors.com/blog
http://lesedi.us