I want to load the results of the query all at once.

meria0503

Currently, we are making it in Excel form based on QTable Widget, but the data loading speed is too slow when DB loads data. I only get 24 lines, but it takes 2 seconds each. Is there any other way?

    QString TIME = ui->selectdateEdit_2->text();
    QString CLOCK = ui->selectdateEdit_3->text();
    QString box = ui->comboBox_Select_2->currentText();
    QSqlQuery query;
    QString sql;

    QSqlDatabase::database().transaction();


    query.setForwardOnly(true);


    sql = QString("SELECT to_char(T_DATE, 'YYYY-MM-DD HH24'),MAX(T_FLOW2) MAX,MAX(T_FLOW1) MAX,to_char(T_DATE,'HH24') GHOUR, count(*) COUNT FROM ULSANSSTBL WHERE (T_DATE>to_date('%1 00:00:00','YYYY-MM-DD HH24:MI:SS') AND T_DATE<to_date('%2 23:59:59','YYYY-MM-DD HH24:MI:SS') AND T_DIVISION='%3' AND T_BLOCK='JB0200') GROUP BY to_char(T_DATE, 'YYYY-MM-DD HH24'),to_char(T_DATE,'HH24') ORDER BY to_char(T_DATE, 'YYYY-MM-DD HH24'),to_char(T_DATE,'HH24')ASC").arg(TIME).arg(CLOCK).arg(box);

    if(!query.exec(sql)){
       qDebug () <<"query error:" << query.lastError();
       } else {
          qDebug () <<"query exec:" << query.lastQuery();



int i = 0;



    while (query.next()) {
            QTableWidgetItem *time = new QTableWidgetItem(query.value(0).toString());
            time->setTextAlignment(Qt::AlignCenter);
            QTableWidgetItem *JB0200 = new QTableWidgetItem(query.value(1).toString());
            JB0200->setTextAlignment(Qt::AlignLeft);
            QTableWidgetItem *JB0200_1 = new QTableWidgetItem(query.value(2).toString());
            JB0200_1->setTextAlignment(Qt::AlignLeft);
            JB0200_1->setData(Qt::EditRole, query.value(2).toInt());

            ui->tableWidget->resizeColumnsToContents();
            ui->tableWidget->horizontalHeader()->setStretchLastSection(true);
            ui->tableWidget->setItem(5+i, 0,new QTableWidgetItem(*time));
            ui->tableWidget->setItem(5+i, 1,new QTableWidgetItem(*JB0200));
            ui->tableWidget->setItem(5+i, 2,new QTableWidgetItem(*JB0200_1));

            i++;
            ui->tableWidget->setRowCount(29);

           }
    }
}

Kent-Dorfman

reading rows from an excel spreadsheet by SQL query, using several aggregate row functions and subselects? ...Hmmm....

And you think it should perform faster than that?

meria0503

@Kent-Dorfman

I don't think there's enough explanation, so if I were to write it down.

QSqlQuery seems to be loading only one Record at a time. I hope to bring it in at a high speed. I checked with qDebug and it takes 0.07 seconds for each row to be loaded, is there any way to get it faster?

Kent-Dorfman

you're missing my point. Excel is not a database and you are trying to make use of database features on it. It is going to be very very slow. max() count() subselects...the excel/sql interface layer most probably doesn't support the level of complexity you are trying to integrate. Even using a real database then your query would perform poorly unless the aggregate fields were indexed and you created it as a "stored query".

If you are set on reading a spreadsheet then you would be further ahead implementing an intermediate step of reading in all the raw data rows and then doing the calculations in the client program.

jsulm

@meria0503 said in I want to load the results of the query all at once.:

ui->tableWidget->setRowCount(29);

Why do you set row count on each iteration?!
Also, why do you call this on each iteration:

ui->tableWidget->resizeColumnsToContents();
ui->tableWidget->horizontalHeader()->setStretchLastSection(true);

Why do you create copy of your QTableWidgetItem here:

ui->tableWidget->setItem(5+i, 0,new QTableWidgetItem(*time));
ui->tableWidget->setItem(5+i, 1,new QTableWidgetItem(*JB0200));
ui->tableWidget->setItem(5+i, 2,new QTableWidgetItem(*JB0200_1));

A lot of unnecessary stuff inside the loop...

meria0503

@Kent-Dorfman

What I'm going to do is bring up the data in the DB and make it into a report form.
Load a spreadsheet file that has already been created, set it up for the QTable Widget cell.

This is how you put it in the date and number you see.

There is no problem loading the data, but it is a problem because it is slow.

jsulm

@meria0503 You should use https://doc.qt.io/qt-6/model-view-programming.html instead of using QTableWidget...

meria0503

@jsulm

I set up the unnecessary code that Jsulm mentioned earlier by taking it out and setting it up, and it became very fast.

I found the cause.

And there is a reason that only QTablewidget is available.This Excel library does not support QTableview.

jsulm

@meria0503 said in I want to load the results of the query all at once.:

This Excel library does not support QTableview

What does QTableview or QTableWidget have to do with Excel "library" (whatever library you mean, I don't know)?!
You're anyway simply executing a SQL query and then MANUALLY adding all the records into QTableWidget. It is way better to use QTableview...