How long does the write operation of the database take

tovax

OS: Win10 64bit
CPU: Intel Core i5-7400 3.00GHz
Memory: 8GB
Database driver: QSQLITE
The operation of writing to the database took about 80 milliseconds. Does it normally take such a long time?
I have tested two writing methods and the results are the same:

QSqlTableModel::setData
QSqlQuery::exec("UPDATE test_table SET ...")

Best regards!

jsulm

@tovax This really depends on many factors, like: how big was the data you wrote, where is the database file located (HDD, SSD, RAM)...

tovax

@jsulm Hi, thanks!
The database is located on the HDD, and just write a double type of data. Is this normal speed?

jsulm

@tovax Never measured SQLite, but would guess writing itself should be faster than 80ms. How exactly do you measure (and what).

tovax

@jsulm

QElapsedTimer timer;
timer.start();
model->setData(index, value, role);
qDebug() << __FUNCTION__ << timer.elapsed();

I have no experience with the read and write speed of the database. I don't know if "80ms" is a normal level.

J.Hilk

@tovax store the elapsed time in a variable beforehand, that qDebug() call may take forever

tovax

@J-Hilk Hi, thanks!

QElapsedTimer timer;
timer.start();
model->setData(index, value, role);
int64_t elapsedBuffer = timer.elapsed();
qDebug() << __FUNCTION__ << elapsedBuffer;

JonB

@tovax
I assume by the way that you have timed this many times in several different places, and taken averages? For example, timing one operation which is, say, the first write to a database/file can be very far from "typical" of your overall throughput rate.

Depending on what timings/operations you want, you may, or may not be interested, in timing multiple writes, and/or writes separated by time, and so on.

If you really want to know what's going, if profiling is available to you fro Creator you could try using that to instrument behaviour.

tovax

Source code:

void CentralMachiningInfo::setValue(const MachiningInfoRow::T row, const QVariant value, int32_t role)
{
    QWriteLocker locker(&modelLock);
    QModelIndex index = model->index(row, MachiningInfoColumn::Value);
    if (model->data(index, role) != value) {
        QElapsedTimer timer;
        timer.start();
        model->setData(index, value, role);
        int64_t elapsedBuffer = timer.elapsed();
        qDebug() << __func__ << elapsedBuffer;
    }
}

Debug output:

setValue 92
setValue 67
setValue 77
setValue 72
setValue 82
setValue 69
setValue 74
setValue 76
setValue 71
setValue 72
setValue 69
setValue 71
setValue 82
setValue 69
setValue 71
setValue 72
setValue 76
setValue 69
setValue 72
setValue 82
setValue 76
setValue 71
setValue 81
setValue 84
setValue 91
setValue 84

JonB

@tovax
Don't forget: setData() may be doing more than just writing a value to the database. For example, it raises a dataChanged() signal, and if you have a view slotted to that (and using the default DirectConnection) you will be timing the whole of the view's response code here.... At minimum you might disable Qt signals for the duration. That's why I said profiling would be the best way to see what is really going on.

Christian Ehrlicher

@tovax said in How long does the write operation of the database take:

QWriteLocker locker(&modelLock);

This looks fishy...

JonB

@Christian-Ehrlicher
Oh yes I didn't notice that! Could be making a huge difference!

@tovax
Comment that out for a start and see whether it is making any difference?

tovax

@JonB
There is no difference after commenting out "QWriteLocker".

Christian Ehrlicher

@tovax said in How long does the write operation of the database take:

There is no difference after commenting out "QWriteLocker".

I don't see why you need it at all - that's why I pointed it out.

tovax

@Christian-Ehrlicher Hi, thank you very much, this class was a singleton class in the previous version, and the current version does not require a read-write lock.

Christian Ehrlicher

So your previous implementation was wrong - you must not access the gui from without the main thread :)

tovax

@Christian-Ehrlicher Thank you very much for your reply :)

tovax

@JonB I have checked the signals and slots of the database and there is no connection during the reading and writing test.

jsulm

@tovax To find out whether the write itself is slow you should omit the model and write directly using QSqlQuery.

JonB

@tovax
Well I'm not sure what you want us to say here? At your timings you can achieve ~15 write operations per second. I would not be very happy with this!

Try:

        timer.start();
        for (int = 0; i < 10; i++)
        {
            model->setData(index, value, role);
            // change `index` and/or `value`
        }
        int64_t elapsedBuffer = timer.elapsed();

Does this take 10x as long??

I have checked the signals and slots of the database and there is no connection during the reading and writing test.

So you have no views at all attached to the model?