Qt and SQL record matching
-
Hello,
I have a hard time understanding how to work efficiently with Qt with SQL-databases. I've been using Qt's QSqlDatabase, QSqlTableModel etc. for a while now, and I've created my own kind of wrapper class around them. It has been working fine, but I've now been working on a project where I need to handle large amounts of database queries and updates/inserts, and now the speed is just not there.
So, I'll make a simple question, which I haven't found and answer after hours of reading the Qt docs and Googling around:
If I have a database table, like this:
@
CREATE TABLE TEST(ID INT, NAME VARCHAR(255));
CREATE UNIQUE INDEX "key0" ON "TEST"(ID);
@And I have data in it like this:
ID NAME
1 ROW 1
2 ROW 2
3 ROW 3
4 ROW 4Okay, If I have a QSqlTableModel of this table, how can I determine the record (or row#, so I can get the record...) of let's say a row with ID = 3? Right now I've created a helper QStringList, where I store the primary key of the table, and then I just call the indexOf -function of QStringList to get the row number, but I just know there must a "real database way" to do this. Working with 200 000+ rows the QStringList indexOf is getting slow I think, and it also might be unreliable in some cases, eventhough it's been working fine so far.
There are other questions I have for optimizing speed working with databases, but this is a start, and it's something I just haven't been able to find out by myself!
-
Best thing, you need to do a deep dive of you DB directly. How Database is going to handle this kind request. Once you know the answer, they can try see whether such feature exist in Qt. Qt is wrapper around database and many convenient functions.
-
I'm using a fairly uncommon database engine, Pervasive SQL. I'm connecting to it using ODBC drivers in Qt. I've tried to read the docs about QModelIndex to check if that's what I'm after, but not.. There doesn't seem to be any function to determine a record from a QSqlTableModel.
What I mean is, I don't really know what features I should be looking for :) I do know there are ways to get the right record from this kind of database, because I've used another programming language to connect to it, and it had a function which you called with the data of the index columns. For example, in my OP I'd call it with:
@
table.find(3)
@and the internal record would populate with the data of the row where ID = 3. If the table has two columns with unique values, you would call the function with two parameters.
This was extremely fast, and that's kind of what I'm looking for here. Now I know Qt's general SQL classes probably won't get THAT fast, but there must be something :(
-
I am working with large tables and many joins since many years with PostgreSQL and I'm currently in the process of changing my frontends to Qt. I work mostly with SQL-queries that will be build by the frontend (now Qt) and use Qt to show the data from my SQL-Database (query.prepare, .exec etc.).
Could this be a practical approach for you? -
I need many queries / insert/updates to the database. Currently I don't directly work with SQL statements, I either insert a new QSqlRecord to the model, or determine which of the current records match the search criteria, and then update the values in that record.
Using SQL statements directly (with query.prepare and .exec) would cause major rework for now. It's not something I won't do if it's absolutely nessecary, but I'd rather not, ofcourse :)
Basically my class now works like so:
table.insertvalue("ID", 1);
table.insertvalue("NAME", "NAME IN ROW 1");
table.post();The post() method checks wether or not the row is a new row (ID is unique) or not. It does this by setting a filter to a QSqlTableModel, selecting the data, and checking if any rows exists. If a row doesn't exist, it inserts a complitely new record, and if it does, it updates the values of the record and submits them to the database. In this case, it would change the NAME -column to NAME IN ROW 1, of the record where ID = 1.
It seems to me that the record check isn't too slow, but inserting/updating rows and then submitting them in QSqlTableModel takes pretty long time. I think one reason for this might be that I'm using OnManualSubmit as a editStrategy, which, as I understand, reselects the data everytime it is posted. Is there a way to make it so it DOESN'T reselect it, using OnManualSubmit editStrategy? I tried the other two, and it was slower, although that was with smaller tables, with bigger tables it might be faster using OnRowChange for example... But the best would be OnManualSubmit, where it doesn't reselect the data (I have a different model for data queries, the model which handles inserts/updates doesn't need to have refreshed data).
-
After experimenting for few days, I've now found one reason for my troubles. Querying has been slow, because the QSqlTableModel::record(int i) function seems to have some kind of bug, which causes it's slowness. I tried it with a database table consisting about 55k rows, doing 55k random row queries though the model object took minutes, when doing the same with a QVector<QSqlRecord> took about 0,25sec.
In my example, table variable consists my own class object, and inside that a QSqlTableModel is initialized. allRecords() -function returns a QVector consisting all the QSqlRecords in the model.
@
QVector<QSqlRecord> records = table.allRecords();/** Takes under a second **/
for (int i = 0; i < records.size(); i++)
{
std::cout << records.at(getrandom(0, records.size()-1)).value("ID").toString().toStdString() << std::endl;
}/** Takes many minutes **/
for (int i = 0; i < table.model->rowCount(); i++)
{
std::cout << table.model->record(getrandom(0, table.model->rowCount()-1)).value("ID").toString().toStdString() << std::endl;
}
@Can anyone confirm this? It seems that calling QSqlTableModel::record(int i) with random row numbers, it gets very slow. If I call with non-random access:
@
for (int i = 0; i < table.model->rowCount(); i++)
{
std::cout << table.model->record(i).value("NUMERO").toString().toStdString() << std::endl;
QCoreApplication::processEvents();
}
@It's a lot faster, but even this takes about 16 seconds, which is a lot more than a simple vector took.
