Re: [Interest] Interest Digest, Vol 91, Issue 26
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 ___ Interest mailing list Interest@qt-project.org https://lists.qt-project.org/listinfo/interest
Re: [Interest] Interest Digest, Vol 91, Issue 26
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. -- Giuseppe D'Angelo | giuseppe.dang...@kdab.com | Senior Software Engineer KDAB (France) S.A.S., a KDAB Group company Tel. France +33 (0)4 90 84 08 53, http://www.kdab.com KDAB - The Qt, C++ and OpenGL Experts smime.p7s Description: S/MIME Cryptographic Signature ___ Interest mailing list Interest@qt-project.org https://lists.qt-project.org/listinfo/interest
Re: [Interest] Interest Digest, Vol 91, Issue 26
On 4/18/2019 5:00 AM, interest-requ...@qt-project.org wrote: Il 17/04/19 23:40, Scott Bloom ha scritto: 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… Mind elaborating? Why not? Cheers, The short answer is that MVVC doesn't work in the real world. The much more in-depth answer takes up a full chapter in my new book. 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. I too have had to deal with this on very large, not even real, databases. You have to create your own model which can store 3 "pages" of results. All of the database I/O has to be done in a background thread (as other posters have found). Doing any real I/O in the primary thread completely locks it up, especially for remote databases on slow transfer paths. The paging part isn't so bad for display. When the visible range gets within X% of either boundary you send off another request to get the next N-hundred rows occurring before or after (depending on direction) the range currently in your model. The difficult part is when they want an "accurate" scrollbar and the non-database database is collecting readings from multiple sensors 5 times per second per sensor. They were using a version of SQLite which meant no triggering of a stored procedure that could communicate to the outside world. This puts a serious burden on the system because your position is relative to the count for the ORDER BY clause of the select. You can somewhat reduce this overhead by performing that count on a timer AND changing the scroll bar unit from row/record to "page". It gets real fun when the client adds the requirement that once a user scrolls to the "end" of current data in a graph the graph should "lock live" updating near real time. It is nice and entertaining to watch, but most of the graphing tools come from a PC mindset, not a real world mindset. They want a full set of data to calculate the axis values. When your model has to lop off a page before adding another it can cause fun times. You can read more about Qt and databases here: http://www.theminimumyouneedtoknow.com/qt_book.html -- 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 ___ Interest mailing list Interest@qt-project.org https://lists.qt-project.org/listinfo/interest
