Qt Sqlite loading data to QComboBox slow and freeze for seconds

[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

//

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:

1. 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.

2. 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.

3. 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:

1. 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.

2. 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.

3. 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!