SQL Server data retrieving speed problem

[Aleksandar]

Hi guys,
I 'm building Qt application based on QTextEdit. The app retrieves data from SQL Server database and inserts it into QTextEdit in the form of table. (Later I create pdf or print)
Everything works fine except for the speed.

Problem:
Data retrieving lasts too long. It seems to me that each time I get record from the QSqlQuery object, the app sends query to SQL Server. Shouldn't QSqlQuery object have buffer with returned data set?

Potential solution:
QSqlTableModel + QTableView works much faster, but I really need that data in the QTextEdit object for inserting some other data and printing!

Does anyone know faster way for this?

Example code:


…
//CREATE & EXECUTE QUERY
QSqlQuery queryAlarms;
queryAlarms.exec(“SELECT …”);
//FULFIL TEXT TABLE WITH RECORDS
FulfilTableWithRecords (queryAlarms, cursor, texttableTable, formatCellFormat2);
…

void AlarmsEditor::FulfilTableWithRecords (QSqlQuery queryAlarms, QTextCursor cursor, QTextTable* texttableTable, QTextCharFormat formatCellFormat2)
{
QSqlRecord record;
QString strAlarmTime;
QString strAckTime;
QString strReturnTime;
QString strTagName;
double doubleValue;
QString strLimit;
QString strKomentar1;
QString strAckOperatorName;
QString strKomentar2;

while(queryAlarms.next())
{
//INSERT ROW IN THE TABLE
texttableTable->appendRows(1);
//GET ONE RECORD
record = queryAlarms.record();
//GET FIELDS FROM THE RECORD
strAlarmTime = record.value(0).toString();
strAckTime = record.value(1).toString();
strReturnTime = record.value(2).toString();
strTagName = record.value(3).toString();
doubleValue = record.value(4).toDouble();
strLimit = record.value(5).toString();
strKomentar1 = record.value(6).toString();
strAckOperatorName = record.value(7).toString();
strKomentar2 = record.value(8).toString();
//CHANGE TIME FORMAT. "2010-07-27T07:27:27" to "2010-07-27 07:27:27"
strAlarmTime.replace("T", " ");
strAckTime.replace("T", " ");
strReturnTime.replace("T", " ");
//FULFIL ONE ROW IN THE TABLE
cursor.insertText(strAlarmTime, formatCellFormat2);
cursor.movePosition(QTextCursor::NextCell);
cursor.insertText(strAckTime, formatCellFormat2);
cursor.movePosition(QTextCursor::NextCell);
cursor.insertText(strReturnTime, formatCellFormat2);
cursor.movePosition(QTextCursor::NextCell);
cursor.insertText(strTagName, formatCellFormat2);
cursor.movePosition(QTextCursor::NextCell);
cursor.insertText(tr("%1").arg(doubleValue), formatCellFormat2);
cursor.movePosition(QTextCursor::NextCell);
cursor.insertText(strLimit, formatCellFormat2);
cursor.movePosition(QTextCursor::NextCell);
cursor.insertText(strKomentar1, formatCellFormat2);
cursor.movePosition(QTextCursor::NextCell);
cursor.insertText(strAckOperatorName, formatCellFormat2);
cursor.movePosition(QTextCursor::NextCell);
cursor.insertText(strKomentar2, formatCellFormat2);
cursor.movePosition(QTextCursor::NextRow);
}
}

[Aleksandar]

It seems like there is no problem with SQL Server data retrieving speed. The QTextTable which I use in QTextEdit to show retrieved data is the one that is slow !!!
Does anyone know how to make QTextTable works faster?!?

[JohannesMunk]

Have you tried to wrap your update code into:

..
texttableTable->setUpdatesEnabled(false);
while (queryAlarms.next())
{
...
}
texttableTable->setUpdatesEnabled(true);

HIH

Joh

[Aleksandar]

Thank you Joh,

This is a valuable note for me, but my app still has speed problem. If you have any more suggestions please share with me!

Aleksandar

[JohannesMunk]

Hi!

I don't know if it will help, but you could try the following:

1) retrieve the number of rows matching your query directly from mysql. => count ?
2) append this number of rows to your text table at once
3) fill the data.

This way the texttable is only resized once. Maybe that increases the speed?

Joh