Results 1 to 10 of 10

Thread: Slow ODBC driver or programming error?

  1. #1
    Join Date
    Nov 2009
    Location
    US, Midwest
    Posts
    215
    Thanks
    62
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Slow ODBC driver or programming error?

    I have a query that returns 1000 rows in about 5 secs on a local network.
    Exactly the same query takes about 5 mins to complete across the internet(fast one).

    I am using odbc driver against MSSQL database.

    I suspect that the code I wrote to retrieve records might be the problem.

    Qt Code:
    1. QAbstractTableModel* DBOperations::executeSelectWithParam (const char* sql, QVariant param)
    2. {
    3. // db is an instance of QSqlDatabase, correctly initialized.
    4. try
    5. {
    6. QSqlQueryModel* retval = new QSqlQueryModel();
    7. retval->setQuery(sql, db);
    8. while (retval->canFetchMore())
    9. retval->fetchMore();
    10. return retval;
    11. }
    12. catch (...)
    13. {
    14. return NULL;
    15. }
    16. }
    To copy to clipboard, switch view to plain text mode 

    Any advice/comment will be appreciated.

  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: Slow ODBC driver or programming error?

    Yeah, skip the while() loop
    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
    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: Slow ODBC driver or programming error?

    If you need a true row count, which is often the reason for the while loop, then it will be more efficient to run a specific query to get the row count and then let Qt's lazy fetching fetch the actual rows only when/if they are needed in a view.

  4. #4
    Join Date
    Aug 2009
    Location
    Belgium
    Posts
    310
    Thanks
    10
    Thanked 31 Times in 25 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: Slow ODBC driver or programming error?

    You can also use setForwardOnly() to make some queries faster.

    Regards,
    Marc

  5. #5
    Join Date
    Nov 2009
    Location
    US, Midwest
    Posts
    215
    Thanks
    62
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Slow ODBC driver or programming error?

    Quote Originally Posted by wysota View Post
    Yeah, skip the while() loop
    Can you explain why? A single query request returns 256 rows in my case. I know that there are 1000+ results, so I am fetching all results from the database.

  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: Slow ODBC driver or programming error?

    Quote Originally Posted by TorAn View Post
    I am fetching all results from the database.
    That's exactly why. You are fetching a lot of data which blocks your app.
    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
    Nov 2009
    Location
    US, Midwest
    Posts
    215
    Thanks
    62
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Slow ODBC driver or programming error?

    1017 records is a lot of data?

    When this call is executed on the machine where db is located it takes about 2 secs.
    When it is executed on the LAN it takes about 4 seconds. When I am on WAN (public), it takes literally 5 minutes. The same sql request through SQL Management studio takes 2 secs on LAN and 2-4 secs on WAN.

  8. #8
    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: Slow ODBC driver or programming error?

    Blame your ODBC driver. Qt driver is optimized for what it does, if you want to force it to load all the rows even if it doesn't want to, you probably get additional queries which slow down everything. Use a better driver or skip the unnecessary while loop.
    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.


  9. #9
    Join Date
    Nov 2009
    Location
    US, Midwest
    Posts
    215
    Thanks
    62
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Slow ODBC driver or programming error?

    Blame your ODBC driver
    That's what I am doing.

    Which driver do you recommend? FreeTDS?

  10. #10
    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: Slow ODBC driver or programming error?

    Use the driver most fit for your database. However I would first think whether I really needed this while loop. It seems not necessary.
    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.


Similar Threads

  1. ODBC function sequence error
    By joseph in forum Qt Programming
    Replies: 9
    Last Post: 7th November 2014, 13:32
  2. source code for odbc driver or MySql driver in arm-embedded-linux
    By sattu in forum Qt for Embedded and Mobile
    Replies: 0
    Last Post: 24th January 2011, 11:11
  3. Model/View Programming - User Error Management
    By Zuzzu in forum Qt Programming
    Replies: 0
    Last Post: 4th March 2009, 17:39
  4. Error Loading MySql Driver in 4.3.4
    By perrigo in forum Qt Programming
    Replies: 5
    Last Post: 26th March 2008, 15:38
  5. Deploying debug Qt application - failed to load ODBC driver
    By sureshbabu in forum Installation and Deployment
    Replies: 1
    Last Post: 1st November 2007, 13:35

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.