Results 1 to 8 of 8

Thread: QSqlQuery poor performance retrieving data

  1. #1
    Join Date
    Oct 2008
    Location
    Brasil - São Paulo - Marília
    Posts
    28
    Thanks
    3
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    3

    Default QSqlQuery poor performance retrieving data

    Hi. I'm exporting some data from a Firebird database to a text file.

    The code is as follow:

    Qt Code:
    1. QSqlDatabase db = QSqlDatabase::addDatabase("QIBASE");
    2.  
    3. db.setHostName("127.0.0.1");
    4. db.setDatabaseName("DATABASE");
    5. db.setPort(3052);
    6. db.setUserName("USER");
    7. db.setPassword("PASS");
    8.  
    9. if (db.open()) {
    10. QSqlQuery query(db);
    11.  
    12. query.prepare(SQL);
    13. query.setForwardOnly(true);
    14.  
    15. if (query.exec()) {
    16. QFile file("C:/Users/NAME/Desktop/Sync.txt");
    17.  
    18. if (file.open(QIODevice::ReadWrite | QIODevice::Truncate)) {
    19. while (query.next()) {
    20. QString rec;
    21.  
    22. for (int i = 0; i < query.record().count(); ++i) {
    23. rec = rec + query.value(i).toString().append("|");
    24. }
    25.  
    26. file.write(rec.append("\n").toStdString().c_str());
    27. }
    28.  
    29. file.close();
    30. } else {
    31. qDebug() << "Error: " << file.errorString();
    32. }
    33. } else {
    34. qDebug() << "Error: " << query.lastError().text();
    35. }
    36. } else {
    37. qDebug() << "Error: " << db.lastError().databaseText();
    38. }
    To copy to clipboard, switch view to plain text mode 

    The table referenced in the SQL has 10k records. And the exported file is around 1MB.

    Using QIBase it takes 05:41 approximately.

    The same table in a MySQL database with the same data takes around 01:13.

    However, this same firebird database accessed through Delphi. Generate this file under 10 seconds.

    Could this poor performance be related to the Qt SQL drivers?

    Is there any way I could improve the overall performance of this type of code?

    Thanks!

  2. #2
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: QSqlQuery poor performance retrieving data

    First of all you can optimize your while loop which does a lot of unnecessary memory allocations and string conversions (you can even generate the string in the database engine directly). To test raw query speed you can remove the whole body of the while loop to see how long it takes to iterate the result.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  3. #3
    Join Date
    Oct 2008
    Location
    Brasil - São Paulo - Marília
    Posts
    28
    Thanks
    3
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    3

    Default Re: QSqlQuery poor performance retrieving data

    Quote Originally Posted by wysota View Post
    First of all you can optimize your while loop which does a lot of unnecessary memory allocations and string conversions (you can even generate the string in the database engine directly). To test raw query speed you can remove the whole body of the while loop to see how long it takes to iterate the result.
    Hi Wysota, thanks for the fast answer.

    I already have tried to minimize memory allocation and string conversions. All with the same result.

    Running the application without the body of the loop is extremely fast as it should be.

    However I solved the problem with a cost, replacing the QIBase plugin by IBPP (http://www.ibpp.org/) library.

    The same code now runs fast enough. QElapsedTimer which I was using to measure the time taken to perform the operation
    is reporting the time as 00:00:00.

    I hope this could help other persons with the same problem using QIBase.

  4. #4
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: QSqlQuery poor performance retrieving data

    Quote Originally Posted by croscato View Post
    I already have tried to minimize memory allocation and string conversions.
    What did you do to achieve the goal? If you still have append() or toStdString() calls then you should try harder, these calls shouldn't be there.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  5. #5
    Join Date
    Oct 2008
    Location
    Brasil - São Paulo - Marília
    Posts
    28
    Thanks
    3
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    3

    Default Re: QSqlQuery poor performance retrieving data

    Dear Wysota the solution I used is the code bellow.

    Qt Code:
    1. IBPP::Driver driver = IBPP::DriverFactory();
    2.  
    3. IBPP::Database db = driver->DatabaseFactory("127.0.0.1/3052", "DATABASE_PATH", "USER", "PASSWORD");
    4.  
    5. db->Connect();
    6.  
    7. IBPP::Transaction transaction = driver->TransactionFactory(db);
    8.  
    9. transaction->Start();
    10.  
    11. IBPP::Statement statement = driver->StatementFactory(db, transaction);
    12.  
    13. statement->Prepare(FBSQL.data());
    14. statement->Execute();
    15.  
    16. QFile file("C:/Users/NAME/Desktop/sync.txt");
    17.  
    18. int total = 0;
    19.  
    20. if (file.open(QIODevice::ReadWrite | QIODevice::Truncate)) {
    21. QElapsedTimer timer;
    22. timer.start();
    23.  
    24. std::string str_data = "";
    25. int64_t int_data = 0;
    26. double dbl_data = 0.0f;
    27.  
    28. QByteArray header;
    29.  
    30. while (statement->Fetch()) {
    31. ++total;
    32.  
    33. if (header.isEmpty()) {
    34. for (int i = 1; i <= statement->Columns(); ++i) {
    35. header.append(statement->ColumnName(i)).append("|");
    36. }
    37.  
    38. header.chop(1);
    39.  
    40. file.write(header.append("\n"));
    41. }
    42.  
    43. QByteArray line;
    44.  
    45. for (int i = 1; i <= statement->Columns(); ++i) {
    46. switch (statement->ColumnType(i)) {
    47. case IBPP::sdArray:
    48. break;
    49. case IBPP::sdBlob:
    50. break;
    51. case IBPP::sdDate:
    52. break;
    53. case IBPP::sdTime:
    54. break;
    55. case IBPP::sdTimestamp:
    56. break;
    57.  
    58. case IBPP::sdString:
    59. statement->Get(i, str_data);
    60.  
    61. line.append(str_data.c_str()).append("|");
    62.  
    63. break;
    64.  
    65. case IBPP::sdSmallint:
    66. case IBPP::sdInteger:
    67. case IBPP::sdLargeint:
    68. statement->Get(i, int_data);
    69.  
    70. line.append(QString::number(int_data)).append("|");
    71.  
    72. break;
    73.  
    74. case IBPP::sdFloat:
    75. case IBPP::sdDouble:
    76. statement->Get(i, dbl_data);
    77.  
    78. line.append(QString::number(dbl_data)).append("|");
    79.  
    80. break;
    81. }
    82. }
    83.  
    84. line.chop(1);
    85.  
    86. file.write(line.append("\n"));
    87. }
    88.  
    89. file.close();
    90.  
    91. qDebug() << "Records: " << total;
    92. qDebug() << "Time: " << msecsToTime(timer.elapsed());
    93. } else {
    94. qDebug() << "Error: " << file.errorString();
    95. }
    96.  
    97. statement->Close();
    98.  
    99. transaction->CommitRetain();
    100.  
    101. db->Disconnect();
    To copy to clipboard, switch view to plain text mode 

  6. #6
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: QSqlQuery poor performance retrieving data

    So where exactly did you try to minimize memory allocations?

    Your original while loop can be rewritten as:

    Qt Code:
    1. QTextStream stream(&file);
    2. while (query.next()) {
    3. QStringList record;
    4. for (int i = 0; i < query.record().count(); ++i) {
    5. record << query.value(i).toString();
    6. }
    7. stream << record.join('|') << endl;
    8. }
    To copy to clipboard, switch view to plain text mode 

    Or even:
    Qt Code:
    1. while(query.next()) {
    2. const int cnt = query.record().count();
    3. for (int i = 0; i < cnt; ++i) {
    4. file.write(query.value(i).toByteArray());
    5. file.write(i == cnt-1 ? '\n' : '|');
    6. }
    7. }
    To copy to clipboard, switch view to plain text mode 
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  7. #7
    Join Date
    Oct 2008
    Location
    Brasil - São Paulo - Marília
    Posts
    28
    Thanks
    3
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    3

    Default Re: QSqlQuery poor performance retrieving data

    Hi wysota.

    I run some tests with the suggestions you made and get some interesting results.

    Every suggest loop were run four times and the measurements are as follow:


    Code snippet 1
    #1: 05:44
    #2: 06:00
    #3: 05:41
    #4: 05:48

    Code snippet 2
    #1: 00:38
    #2: 00:28
    #3: 00:29
    #4: 00:38


    As you can see. The second code snippet runs reasonably faster.

    Seeing this results I got intrigued about what could have caused this large difference in execution time.

    Then a tested this modified version of the code snippet 1

    Qt Code:
    1. QTextStream stream(&file);
    2.  
    3. const int cnt = query.record().count();
    4.  
    5. while (query.next()) {
    6. QStringList record;
    7. for (int i = 0; i < cnt; ++i) {
    8. record << query.value(i).toString();
    9. }
    10. stream << record.join('|') << endl;
    11. }
    To copy to clipboard, switch view to plain text mode 
    and the results were:


    Code snippet 3
    #1: 00:35
    #2: 00:30
    #3: 00:40
    #4: 00:45


    Close enough from the performance achieved with code snippet 2.

    It seems that querying the record count in all iterations of the loop was the real bootleneck and the code snippet 2 is the answer for this problem, although using IBPP I can obtain a little more performance.

  8. #8
    Join Date
    Oct 2008
    Location
    Brasil - São Paulo - Marília
    Posts
    28
    Thanks
    3
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    3

    Default Re: QSqlQuery poor performance retrieving data

    After finishing my project I decide to investigate what could have been causing the slow response in Qt SQL classes.

    Looking at QSqlQuery code
    https://qt.gitorious.org/qt/qtbase/s.../qsqlquery.cpp line #905, the method record() seems to be the problem.

    Qt Code:
    1. QSqlRecord QSqlQuery::record() const
    2. {
    3. QSqlRecord rec = d->sqlResult->record();
    4.  
    5. if (isValid()) {
    6. for (int i = 0; i < rec.count(); ++i)
    7. rec.setValue(i, value(i));
    8. }
    9. return rec;
    10. }
    To copy to clipboard, switch view to plain text mode 
    As you can see the code loops through an internal record structure and populates a QSqlRecord which is returned by the method. Putting a call to record() inside any loop, now doesn't look like a good idea.

    I made a project to test calling record() in diferent ways. And exporting data with 300K records I get results varying from 03:00 to 00:30 using three different implementations.

    Project: QIBaseExport.zip
    Database DDL: employee.sql.bz2

    I coundn't attach the data file because it's size exceeds the Qt Centre limitation. But this database it's based in MySQL open database sample.
    All data and table definition could be downloaded from https://launchpad.net/test-db/

    Anyway, before anything I should have read Qt documentation as it says from the record method:

    Returns a QSqlRecord containing the field information for the current query. If the query points to a valid row (isValid() returns true), the record is populated with the row's values. An empty record is returned when there is no active query (isActive() returns false).

    To retrieve values from a query, value() should be used since its index-based lookup is faster.

    In the following example, a SELECT * FROM query is executed. Since the order of the columns is not defined, QSqlRecord::indexOf() is used to obtain the index of a column.
    and yet provides an example of how it should be used

    Qt Code:
    1. QSqlQuery q("select * from employees");
    2. QSqlRecord rec = q.record();
    3.  
    4. qDebug() << "Number of columns: " << rec.count();
    5.  
    6. int nameCol = rec.indexOf("name"); // index of the field "name"
    7.  
    8. while (q.next())
    9. qDebug() << q.value(nameCol).toString(); // output all names
    To copy to clipboard, switch view to plain text mode 
    note that the call to record is done only once and outside to while loop.

Similar Threads

  1. DashLine drawing poor performance
    By jecaro in forum Qt Programming
    Replies: 0
    Last Post: 11th August 2010, 14:46
  2. QTableView - resizeRowsToContents() poor performance
    By antarctic in forum Qt Programming
    Replies: 2
    Last Post: 11th December 2009, 14:13
  3. Poor OpenGL performance
    By rakkar in forum Newbie
    Replies: 1
    Last Post: 3rd September 2009, 20:51
  4. Qt4 poor printing performance
    By seneca in forum Qt Programming
    Replies: 4
    Last Post: 22nd January 2009, 15:23
  5. Poor performance with Qt 4.3 and Microsoft SQL Server
    By Korgen in forum Qt Programming
    Replies: 2
    Last Post: 23rd November 2007, 11:28

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.