Results 1 to 3 of 3

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

  1. #1
    Join Date
    Nov 2015
    Location
    Vermont
    Posts
    46
    Thanks
    13
    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,533
    Thanks
    13
    Thanked 1,576 Times in 1,505 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
    46
    Thanks
    13
    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.

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.