Results 1 to 4 of 4

Thread: MySql model/view vs. ODBC model/view speed differences

  1. #1
    Join Date
    Nov 2015
    Location
    Vermont
    Posts
    52
    Thanks
    15
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    MacOS X Windows

    Default MySql model/view vs. ODBC model/view speed differences

    Scenario:
    We are going to be moving from retail system using a MySql Database to a new system that uses SQL Server. I am working on rewriting a very simple program that puts the results of a simple SQL select statement into a QSqlQueryModel, which is then used as a source model for a QSortFilterProxyModel (to allow the user to sort on columns) and displayed in a QTableView. With MySql the data is returned and displayed basically instantaneously, while the same amount of data (100 rows by ~20 columns) using ODBC takes about 10 seconds to load into the QTableView. From what I can tell, the database returns results in only about a half a second longer, and it's the model(s) that is extremely slow. If I don't use the QSortFilterProxyModel, the data shows up in the table very quickly, but there's a 5 second or so lockup before I can scroll around the table or click on anything. Also, calling resizeColumnsToContents() on the tableview returns the lag of data appearance back to about 10 seconds. It should also be noted that I do not believe the SQL query itself is the issue here, because when I call the exact same query in Microsoft SQL Server Management Studio it returns instant results.

    Questions:
    1. Why the difference in speed? I know that the QODBC driver doesn't return a query size, is this the root of the problem?
    2. If this is an inherent problem, is there a way around it? I know that people recommend setting setForwardOnly() on the query to true, but this is not allowed with a QSqlQueryModel and I didn't find that it dramatically increased speeds anyway - it is really such a small amount of data.

    Of Note:
    I didn't include any code because it didn't seem entirely necessary, but please let me know if it would help clarify anything and I can add it.

  2. #2
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: MySql model/view vs. ODBC model/view speed differences

    Where is the extra time spent? In making the connection in the first place, executing the query, retrieving the result of the query, or somewhere else?
    Last edited by ChrisW67; 9th March 2019 at 03:04. Reason: spelling corrections
    "We can't solve problems by using the same kind of thinking we used when we created them." -- Einstein
    If you are posting code then please use [code] [/code] tags around it - makes addressing the problem easier.

  3. #3
    Join Date
    Nov 2015
    Location
    Vermont
    Posts
    52
    Thanks
    15
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    MacOS X Windows

    Default Re: MySql model/view vs. ODBC model/view speed differences

    Quote Originally Posted by ChrisW67 View Post
    Where is the extra time spent? In making the connection in the first place, executing the query, retrieving the result of the query, or somewhere else?
    As far as I can tell, the extra time is entirely after the results of the query are received. The results are returned within a normal time frame, but any manipulation of the model or view (e.g., filtering, sorting, resizing columns to contents) takes ages. I ended up giving up on using a QSqlTableModel and QTableView approach and simply went to using a QSqlQuery and a QTableWidget. It's about 1 second slower with SQL Server than it was with MySql, but that's manageable for me. In all of the other questions and answers I found related to my situation, everyone just ended up doing this.

  4. #4
    Join Date
    Nov 2006
    Location
    Dresden, Germany
    Posts
    108
    Thanks
    9
    Thanked 12 Times in 10 Posts
    Qt products
    Qt4
    Platforms
    MacOS X Unix/X11 Windows

    Exclamation Re: MySql model/view vs. ODBC model/view speed differences

    I can confirm the observation - also moving data from SQL Server DB via ODBC to Postgres Database.

    The following code executes fairly quickly:

    Qt Code:
    1. QSqlDatabase odbcsql = QSqlDatabase::addDatabase("QODBC", "odbcsql");
    2.  
    3. ...
    4.  
    5. QSqlDatabase odbcsql = QSqlDatabase::database("odbcsql");
    6.  
    7. // lesen
    8. QSqlTableModel tabModelSource(nullptr, odbcsql);
    9. tabModelSource.setTable(sTableSource);
    10. if (!tabModelSource.select()) {
    11. qCritical() << tabModelSource.lastError();
    12. return;
    13. }
    14.  
    15. int r = tabModelSource.rowCount();
    16. while (tabModelSource.canFetchMore()) {
    17. tabModelSource.fetchMore();
    18. }
    19. qDebug() << "Total number of rows =" << r;
    To copy to clipboard, switch view to plain text mode 

    This code takes less than a second for 160000 entries - way too many records to transfer in 1 seconds over my slow internet connection. So apparently data is not transferred and cached in the calls to fetchMore(), yet.

    Now, when I start to access the data:

    Qt Code:
    1. qDebug() << "Caching table data";
    2. std::vector<QList<QVariant> > data;
    3. for (int i=0; i<r; ++i) {
    4. QSqlRecord rec(tabModelSource.record(i)); // <--- very slow access
    5. QList<QVariant> vals;
    6. for (int j=0; j<rec.count(); ++j)
    7. vals.append(rec.value(j));
    8. data.push_back(vals);
    9. }
    To copy to clipboard, switch view to plain text mode 

    Every time I access a record, the data is crawling over my internet connection at about 30...56kB/s (and my network really isn't than slow!)

    Conclusion:

    1. ODBC-driver doesn't do any caching
    2. ODBC-driver is basically unusable for real-life data in Qt model/view classes

    Questions:

    Does anyone know how to tell the ODBC-driver to retrieve the data in larger chunks, instead value-by-value?

    -Andreas
    Andreas

Similar Threads

  1. Replies: 4
    Last Post: 18th April 2012, 18:11
  2. Replies: 0
    Last Post: 21st April 2010, 12:23
  3. Replies: 2
    Last Post: 17th February 2010, 14:32
  4. Replies: 1
    Last Post: 1st February 2010, 18:42
  5. QSql Model-View: How to keep view in sync with model
    By schall_l in forum Qt Programming
    Replies: 1
    Last Post: 22nd December 2008, 23:31

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.