Form with 20 QComboxBox => 20 QSqlQuery => 2,0 sec to refresh the window?

SebastienL

Hi everybody,

I've got a small problem. I recently developed a desktop app with a MySQL connection, which works nicely with a localhost MySQL server (no lag).
The problems come when I decided to give the tool to other people in the team, so I put the MySQL server... on a distant server (a VPS).

I have like 20 QComboBox on my form, and each is populated with data from the MySQL server. My problem is now with the server online, each SQL query takes something like 100ms to execute, so just to load the main form, my desktop app freeze for 2,0 sec.

I can't find a way to execute the 20 query in parallel, or with just one query and ";" between them (multiple statement).
Did I miss an obvious way to avoid freezing the form at each load?

Regards,
Sebastien.

JonB

@SebastienL
I assume you do mean you have 20 separate tables/selects to populate 20 quite distinct combo boxes. I don't think @Christian-Ehrlicher's "reuse the QSqlQuery object" will help. I don't think you refresh every time, only one off at the start.

You might delay the query to populate each combo box until first use (dropdown click), or read them separately on a short-delay QTimer after first show of the form, to "spread" the startup time, but I know that can be a hassle.

I do not know whether/how QSqlQuery can handle multiple statements/result sets. It might be possible to push the SQL queries off to their own thread and use signals/slots to call & populate the combo boxes. But all SQL queries must be executed in the same thread as the declaration of the SQL database, so this has ramifications for the overall usage of SQL from your code.

If you cannot find a way of executing a multi-statement-result-set, for the particular case of getting the values to populate multiple lists I will offer you the following single statement (assumes the values in each table are all of the same type, e.g string):

SELECT "list1", value_column_name1 FROM table1
UNION ALL
SELECT "list2", value_column_name2 FROM table2
UNION ALL
SELECT "list3", value_column_name3 FROM table3
UNION ALL
...

You can then discern which values are associated with which lists via the literal value in the first column returned. You will then have to separate those out into distinct 20 lists to use with each combo box, or be clever in code to retain in a single model testing the first column at runtime.

If you do find how to do multiple separate-result-set queries, let us know!

Christian Ehrlicher

Don't re-create the QSqlQuery every time but reuse the QSqlQuery object would be one option.
Also why do you want to refresh everything everytime?

JonB

@SebastienL
I assume you do mean you have 20 separate tables/selects to populate 20 quite distinct combo boxes. I don't think @Christian-Ehrlicher's "reuse the QSqlQuery object" will help. I don't think you refresh every time, only one off at the start.

You might delay the query to populate each combo box until first use (dropdown click), or read them separately on a short-delay QTimer after first show of the form, to "spread" the startup time, but I know that can be a hassle.

I do not know whether/how QSqlQuery can handle multiple statements/result sets. It might be possible to push the SQL queries off to their own thread and use signals/slots to call & populate the combo boxes. But all SQL queries must be executed in the same thread as the declaration of the SQL database, so this has ramifications for the overall usage of SQL from your code.

If you cannot find a way of executing a multi-statement-result-set, for the particular case of getting the values to populate multiple lists I will offer you the following single statement (assumes the values in each table are all of the same type, e.g string):

SELECT "list1", value_column_name1 FROM table1
UNION ALL
SELECT "list2", value_column_name2 FROM table2
UNION ALL
SELECT "list3", value_column_name3 FROM table3
UNION ALL
...

You can then discern which values are associated with which lists via the literal value in the first column returned. You will then have to separate those out into distinct 20 lists to use with each combo box, or be clever in code to retain in a single model testing the first column at runtime.

If you do find how to do multiple separate-result-set queries, let us know!

SebastienL

@JonB Awesome way to get multiple results with a single statement query, thank you very much!
As you said, I will try to be clever too with the structure of my tables, to be able to apply this solution efficiently too.
I will try this, and keep you updated.

Regards

SebastienL

Hi @JonB ,
After some tries, your solution with the UNION works indeed.
Just for pleasure, I decided to make a try with the MySQL library directly, instead of QSqlQuery.
This allows to execute multiple statement query and multiple resultset, and different data type from the queries.

Here is my code for that (which work for me), I used package libmysqlcppconn9, libmysqlcppconn-dev (and probably libmysqlclient-dev) :

mySQLdBManager.cpp :

#include "mySQLdBManager.h"

using namespace std;

MySQLdBManager::MySQLdBManager()
{
    qDebug() << "[MySQLdBManager] Constructor()";

    cout << endl;


    try
    {
        sql::Driver *driver;
        sql::Connection *con;
        sql::Statement *stmt;
        sql::ResultSet *res;

        // Create a connection
        //driver = get_driver_instance();
        driver = sql::mysql::get_mysql_driver_instance();

        cout << "Connection to MySQL server..." << endl;

        sql::ConnectOptionsMap connection_properties;
        connection_properties["hostName"] = "tcp://99.99.99.1";
        connection_properties["userName"] = "baseDocumentsUser";
        connection_properties["password"] = "passwordxx";
        connection_properties["port"] = 3306;
        connection_properties["CLIENT_MULTI_STATEMENTS"] = true;
        connection_properties["OPT_RECONNECT"] = true;
        con = driver->connect(connection_properties);

        con->setSchema("baseDocuments"); // Select the dabase
        cout << "Connection to MySQL server... OK (con->isValid() return " << con->isValid() << ")" << endl;

        cout << "Running multi statement 'SELECT * FROM T_CATEGORIES1;SELECT * FROM T_CATEGORIES2'..." << endl;

        stmt = con->createStatement();

        stmt->execute("SELECT * FROM T_CATEGORIES1;SELECT * FROM T_CATEGORIES2");

        int numResultSet = 0;
        do
        {
            res = stmt->getResultSet();
            cout << "\t... MySQL dataset number " << numResultSet << " : " << endl;
            while (res->next()) {
                cout << "\t... MySQL query result data : ";
                //cout << res->getString("CAT1_STR") << endl; // Access column data by column name
                cout << res->getString(3) << endl; // Access column data by column name or column index (here third column)
            }
            numResultSet++;
        } while (stmt->getMoreResults());

        delete res;
        delete stmt;
        delete con;


    }
    catch (sql::SQLException &e)
    {
        cout << "# ERR: SQLException in " << __FILE__;
        cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
        cout << "# ERR: " << e.what();
        cout << " (MySQL error code: " << e.getErrorCode();
        cout << ", SQLState: " << e.getSQLState() << " )" << endl;
    }

    cout << endl;

}

mySQLdBManager.h :

#ifndef MYSQLDBMANAGER_H
#define MYSQLDBMANAGER_H

#include <QDebug>

#include <mysql_driver.h>
#include <mysql_connection.h>

#include <cppconn/resultset.h>
#include <cppconn/statement.h>

class MySQLdBManager
{

public :
    MySQLdBManager();

};
#endif // MYSQLDBMANAGER_H

and I add that in the .pro file to compile well :

INCLUDEPATH += /usr/include/mysql-cppconn-8/jdbc/
LIBS+=/usr/lib/x86_64-linux-gnu/libmysqlcppconn.so.9

JonB

@SebastienL
Happy the UNION worked, it was a kludge.

You know more than I about MySQL libraries. It would always be better to solve what you wanted via a multi-query.

I did not find that Qt's QSqlDatabase driver for MySQL allowed this. It would be nice if a Qt SQL expert could confirm that it does not support multi-statement-queries?