Qt Sqlite loading data to QComboBox slow and freeze for seconds

Proton Phoenix

i have a problem with loading specified data from sqlite table to QComboBox , it's really very slow and freeze the software screen for 3 to 5 second until it gets the specified data

alt text

the first right button this is the code of it's button

void facture::on_loadbuyer_clicked()
{
     facture conn3; // an object ...
           // creating a a Query object (pointer)
           QSqlQueryModel * model = new QSqlQueryModel();
           // opening the connection again
           conn3.DOpen(); // this is a function inside header file used for opening SQLITE Connection
           QSqlQuery* qry=new QSqlQuery(conn3.db);
            qry->prepare("SELECT Name FROM customers");
           qry->exec();
           model->setQuery(*qry);
           ui->nameofbuyercombobox_1->setModel(model);
}

and the second button this is it's code :

void facture::on_loaddatabasebutton_5_clicked()
{
    facture conn4;
       // creating a a Query object (pointer)
       QSqlQueryModel * model = new QSqlQueryModel();
       // opening the connection again
       conn4.conOpen();
       QSqlQuery* qry=new QSqlQuery(conn4.db);
        qry->prepare("SELECT Name_Products FROM product");
       qry->exec();
       qry->lastError();
       model->setQuery(*qry);
       ui->comboBox_1->setModel(model);
}

then using slots of Combobox to show barcode and Name_Products Price (related to button which is in the center) this is the code :

void facture::on_comboBox_1_currentIndexChanged(int index)
{
    QSqlQuery qry(db);
    QString search = ui->comboBox_1->currentText();
        qry.prepare("SELECT * FROM product WHERE (Name_Products = '"+search+"') ");
        if(qry.exec()){
            while (qry.next()){
         qDebug() << qry.lastError();
         ui->barcode_1->setValue(qry.value(0).toInt());
         ui->comboBox_1->setCurrentText(qry.value(1).toString());
         ui->priceofbuy_1->setValue(qry.value(3).toFloat());

}
        }
}

my problem the data load correctly but very slow really very slow from 3 to 6 seconds to load 10 of data to QComboBox My pc : i3 4160 with 12 gb ram ddr3

JonB

@Proton-Phoenix

Remove the combo box from your timings, and verify what is going on just executing the queries without that complication.
Why are you creating new models/connections each time? Try with an existing model & connection and see if that explains the overhead.

Proton Phoenix

@JonB said in Qt Sqlite loading data to QComboBox slow and freeze for seconds:

your

//
alt text
after removing comboBox from my timings code just clicking to the right button (loadbuyer) will freeze the software for 3 to 6 until it back!
about Why are you creating new models/connections each time? Try with an existing model & connection and see if that explains the overhead.
i have just 6 month QT experience i still a beginner in connections that's why i am using new models connections each time
but in this situation i removed all slots ... only the button right and the center one works (two buttons with two combobox
-- remark if i remove the stylesheet .. it became faster about 3 seconds .....
is there any solution?

artwaw

@Proton-Phoenix Hi. In my opinion there are multiple not very efficient approaches. Let's try to sort this out:

if I understood parts of your code correctly you introduce new connection (with opening and such) after each push of the button. That is not necessary! One connection is sufficient. Try to init your db default connection in the constructor of the main class, for the ease of example:

auto db = QSqlDatabase::addDatabase('QSQLite');
db.setDatabaseName('path_to_your_db_file');
if (!db.open()) {
    //here what you want to do if file cannot be open
}

After successfully opening the database you have permanent (let's say) default connection that can be reused.
If I understand your code correctly you are not using threads for interactions with your UI so that should really suffice.
Please also note that db above should not be stored as a member of any class, it can safely go out of the scope.

Models. For some reason you are using QSqlQuery, which is very primitive for your purpose. I'd suggest using QSqlTableModel - a class that will automate quite a bit for you. As the name suggests you can go with one model per table. So let's assume that in your header file you've got:

QSqlTableModel *customersModel;
QSqlTableModel *productModel;

Now somewhere in your code you need to initialise those models. Again, this needs to be done only once so if you place the code just after opening db connection you can reuse db. Otherwise you'll need to recall the default connection which can be easily done with auto db = QSqlDatabase::database(true);

customersModel = new QSqlTableModel(this,db);
customersModel->setTable('customers');
customerModel->select(); //here the model is populated with data;
productModel = new QSqlTableModel(this,db);
productModel->setTable('product');
productModel->select();

Now - how to apply that to your UI? Let's take your first method:

ui->nameofbuyercombobox_1->setModel(customersModel);
ui->nameofbuyercombobox_1->setModelColumn(number_of_column_with_customer_name); //please note that columns numbering starts with zero

And that is mostly it. For starters.

The slot for combo box index changed signal:
it is usually faster (although requires a bit more work on your side) to utilise QSortFilterProxyModel - you connect it between your model (in our test case here it would be productModel) and the view. The documentation describes exactly the kind of filtering you need to I am leaving it for you to have some fun.
Why this way? Well, for starters you don't call the query every single time someone types the character in the filter. It all happens in the memory, time saving can be enormous.
Secondly, select '*' returns unsorted results, with this model you can have nice user experience.
And, lastly, the whole codebase is easier to read I think.
Anyway, that is how I would approach your problems.

Proton Phoenix

@artwaw Really Thank you bro <3 i will try that i am sure it will work ^^
you understand my code very well i didn't use threads ... etc
<3

artwaw

@Proton-Phoenix Always happy to help. Also, your code might benefit from using this class https://doc.qt.io/qt-5/qdatawidgetmapper.html#details - but can easily go without it too. Sometimes it is just easier to manage connections between models and multiple widgets that way.

Proton Phoenix

@artwaw said in Qt Sqlite loading data to QComboBox slow and freeze for seconds:

@Proton-Phoenix Always happy to help. Also, your code might benefit from using this class https://doc.qt.io/qt-5/qdatawidgetmapper.html#details - but can easily go without it too. Sometimes it is just easier to manage connections between models and multiple widgets that way.

really thank you for your great help bro <3 , notice i have ask this question in many famous sites .... stack.... no one found the solution exception you ... really many thanks <3

Proton Phoenix

@artwaw said in Qt Sqlite loading data to QComboBox slow and freeze for seconds:

@Proton-Phoenix Hi. In my opinion there are multiple not very efficient approaches. Let's try to sort this out:

if I understood parts of your code correctly you introduce new connection (with opening and such) after each push of the button. That is not necessary! One connection is sufficient. Try to init your db default connection in the constructor of the main class, for the ease of example:
auto db = QSqlDatabase::addDatabase('QSQLite');
db.setDatabaseName('path_to_your_db_file');
if (!db.open()) {
    //here what you want to do if file cannot be open
}
After successfully opening the database you have permanent (let's say) default connection that can be reused.
If I understand your code correctly you are not using threads for interactions with your UI so that should really suffice.
Please also note that db above should not be stored as a member of any class, it can safely go out of the scope.

Models. For some reason you are using QSqlQuery, which is very primitive for your purpose. I'd suggest using QSqlTableModel - a class that will automate quite a bit for you. As the name suggests you can go with one model per table. So let's assume that in your header file you've got:
QSqlTableModel *customersModel;
QSqlTableModel *productModel;
Now somewhere in your code you need to initialise those models. Again, this needs to be done only once so if you place the code just after opening db connection you can reuse db. Otherwise you'll need to recall the default connection which can be easily done with auto db = QSqlDatabase::database(true);
customersModel = new QSqlTableModel(this,db);
customersModel->setTable('customers');
customerModel->select(); //here the model is populated with data;
productModel = new QSqlTableModel(this,db);
productModel->setTable('product');
productModel->select();
Now - how to apply that to your UI? Let's take your first method:
ui->nameofbuyercombobox_1->setModel(customersModel);
ui->nameofbuyercombobox_1->setModelColumn(number_of_column_with_customer_name); //please note that columns numbering starts with zero
And that is mostly it. For starters.

The slot for combo box index changed signal:
it is usually faster (although requires a bit more work on your side) to utilise QSortFilterProxyModel - you connect it between your model (in our test case here it would be productModel) and the view. The documentation describes exactly the kind of filtering you need to I am leaving it for you to have some fun.
Why this way? Well, for starters you don't call the query every single time someone types the character in the filter. It all happens in the memory, time saving can be enormous.
Secondly, select '*' returns unsorted results, with this model you can have nice user experience.
And, lastly, the whole codebase is easier to read I think.
Anyway, that is how I would approach your problems.

after trying this , it works really very fast now , many thanks bro <3

artwaw

@Proton-Phoenix I am happy for you. Please mark the topic as "solved" if you are happy with the outcome.

Proton Phoenix

bro <3 <<"artwaw"> after trying this

QSqlTableModel *productModel;
productModel = new QSqlTableModel(this,db);
productModel->setTable("products");
productModel->select();
QDataWidgetMapper *mapper = new QDataWidgetMapper;
mapper->setModel(productModel);
mapper->addMapping(ui->barcode_1, 0);
mapper->addMapping(ui->comboBox_1, 1);
mapper->addMapping(ui->priceofbuy_1, 3);
mapper->toFirst();

i don't know what i may do without this help from you . you gave me everything i need in small topic <3 i wish happy great life for you bro really thanks

artwaw

@Proton-Phoenix Thank you!