QSortFilterProxyModel vs SQLite (with FTS3)? in term of performance and memory usage

Christian Ehrlicher

@mbruel said in QSortFilterProxyModel vs SQLite (with FTS3)? in term of performance and memory usage:

haha thanks. I would have imagined that Qt could provide such things to kind of "benchmark" their Proxy mechanism... or that some people would have done it to see how it reacts under heavy load.

It all depends on the data - every benchmark will be wrong for any other usecase.

mbruel

@Christian-Ehrlicher said in QSortFilterProxyModel vs SQLite (with FTS3)? in term of performance and memory usage:

It all depends on the data - every benchmark will be wrong for any other usecase.

yes that's true, but it could give a general idea and maybe warn about bottlenecks... like 5% match (text search) on X records (10k, 100k, 1M) of text length 1000 chars for example.

Well, as I'm learning QML, I've chosen the Proxy way using a self populated tree Model.
It's working great, even on an old iPhone SE :)

I lost most of the time to tweak the deprecated TreeView from Controls 1.4... what a shame it is not in the v2 oO I hope it will be back with Qt6... It is definitely needed even if it is an hassle to understand how to render it (especially due to the necessary TableViewColumn....)

For those interested, I filter up and down the tree ProxyModel for each row in order to make the filtering decision. It works smoothly with 40k records.
Here is the filtering method.

@JonB and here is the way I populate the model from SQL. with the request being:

const QString ClementineRemote::sLibrarySQL =
        "select artist, album, title, track, filename from songs order by artist, album, track, title";

fcarney

Have you played with QSqlTableModel? It is based upon the abstract table model. I am going to be using this in a project and am curious if you compared its speed.

mbruel

@fcarney no I've not tried QSqlTableModel. As explained I wanted a Tree not a table.
alt text

Christian Ehrlicher

@mbruel said in QSortFilterProxyModel vs SQLite (with FTS3)? in term of performance and memory usage:

ike 5% match (text search) on X records (10k, 100k, 1M) of text length 1000 chars for example.

How should this be possible? It depends on your implementation and your data...

mbruel

@Christian-Ehrlicher it should only depend on the data. the idea would be to produce a data set of N records with length around a determined number of chars l.
Then you provide texts to search that would match an exact percentage p.
you can do that using whatever DB. Normally if you export the data set in another one or in whatever program, the output should be the same. You can thus compare the execution time. Does it make sense?

Christian Ehrlicher

@mbruel said in QSortFilterProxyModel vs SQLite (with FTS3)? in term of performance and memory usage:

Does it make sense?

Not really - how to you compare texts is very important, also if you don't compare text but numbers and so on.
You can write a testcase on your own to check it.

mbruel

@Christian-Ehrlicher the idea would have been to test comparison implementation on text only, for example a LIKE "%<text>%"
but I see what you mean.
I guess this kind of testing is done by companies to decide which tool/implementation to use on products where performance is critical. Myself, I don't have this need...

JonB

@mbruel said in QSortFilterProxyModel vs SQLite (with FTS3)? in term of performance and memory usage:

LIKE "%<text>%"

Nothing to do with your proposed timing tests. Just want to make sure you are aware: there will (should) be an enormous time difference between LIKE "%<text>%" versus LIKE "<text>%".

mbruel

@JonB

Nothing to do with your proposed timing tests

maybe I wasn't clear but that's what I'm asking since the beginning. my initial goal was to use a QSortFilterProxyModel and set its regexp (cf here), so I was planing to do a LIKE "%<text>%". In fact even worst for the perf as in Qt I can do it case insensitive.

My concern was to know how QSortFilterProxyModel handle a big/huge number of records. I'm testing with 40k, it is loading and filtering in few hundreds milliseconds. What about with 100k or 1M records?
That is why I was considering using SQL instead. Why not FTS even, as I said since the beginning, if it would limit the search to full word or like you told me a "search for left-hand side match".

So basically I was interested in any kind of benchmarks for searching either a regexp, a full word or a left-hand side match. Benchmark/Feedback on QSortFilterProxyModel and why not a comparison with SQLite or another DB.

Edit: as you could imagine from the picture above, my textfield trigger a search each time the text changes (for each char :))
Edit2: and yes I'm aware of the difference, I was just willing to accept limiting the search if it was lagging too much