Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. FetchMore with ODBC driver extremely slow
Forum Updated to NodeBB v4.3 + New Features

FetchMore with ODBC driver extremely slow

Scheduled Pinned Locked Moved General and Desktop
7 Posts 4 Posters 1.7k Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • Z Offline
    Z Offline
    Zerby
    wrote on last edited by
    #1

    Populating a QSqlQueryModel with a large database table consisting over 100k rows is extremely slow. If I use:

    @
    while (this->canFetchMore())
    {
    this->fetchMore();
    }
    @

    A database table consisting over 100k rows is pretty much never loaded fully, because the it get's slower and slower to get the next 256 rows. The first 20k or so is fetched reasonably fast, but after that it get slower and slower and slower, eventually slowing so much it pretty much halts.

    Does anyone have any experience on this? I'm using the ODBC driver that I built from Qt sources. I found a post with Google which says:

    "So the problem comes from the QODBCResult behaviour. I downloaded Qt's Sources, and edit odbc driver. For those interrested in it, you need to change the behaviour of QODBCResult::data(int) and at least QODBCResult::fetch(int) (and maybe QODBCResult::fetchprevious/next/last/first if you really want something clean) functions.

    I personnally add a QList buffer, where I store my rows when they are requested. It's almost the same behaviour as Qt (since it don't cache everything but only the requested rows). My app take up to 38-40 Mbs in memory for 22k rows / 55 columns of text and floats in cache."

    I looked at the sources, but I must say I can't really see how to optimize it, not enough skills as of yet... :) Can someone help me, maybe someone has come across the same issue?

    1 Reply Last reply
    0
    • J Offline
      J Offline
      John Doe1
      wrote on last edited by
      #2

      I have the same problem. I've Query result for 100k, and process this data per 3-4 min, that very slow. In QT you can't set FETCH SIZE for QODBC driver in standart function to set other options, as setConnectOptions(const QString &options = QString()). For QOCI driver you can change FETCH SIZE, OCI_ATTR_PREFETCH_ROWS, but for QODBC you can't. I'm not find solution of this problem. Some peoples tell, that they use other libs and drivers for work with DB, not QODBC driver. For example IDBB dirver work very fast.

      Please can you post your changes. Are you change qsql_odbc.cpp file, right?

      1 Reply Last reply
      0
      • hskoglundH Online
        hskoglundH Online
        hskoglund
        wrote on last edited by
        #3

        Hi, don't know if it helps you, but have you tried setting your queries to forward only, e.g.:
        QSQLQuery q;
        q.setForwardOnly(true);
        (made my queries about 50% faster).

        JonBJ 1 Reply Last reply
        1
        • hskoglundH hskoglund

          Hi, don't know if it helps you, but have you tried setting your queries to forward only, e.g.:
          QSQLQuery q;
          q.setForwardOnly(true);
          (made my queries about 50% faster).

          JonBJ Offline
          JonBJ Offline
          JonB
          wrote on last edited by JonB
          #4

          @hskoglund
          This is a good tip. It also saves on memory. It may be that the speed/space saving is even greater with the large number of rows in question here.

          A separate issue is why/whether the OP needs to read all 100k rows into memory --- surely not to display them?

          1 Reply Last reply
          0
          • J Offline
            J Offline
            John Doe1
            wrote on last edited by
            #5

            @hskoglund, Hi, thanks for answer. Of course, every query i set query.setForwardOnly(true);
            before call query.exec(); in qsql_odbc.cpp i see "static const int COLNAMESIZE = 256;"
            i think that COLNAMESIZE look like OCI_ATTR_PREFETCH_ROWS for QOCI. I try to change COLNAMESIZE in qsql_odbc.cpp from 256 to 1024, and rebuild dll from source code. I rebuild dll, copy this new dll qsqlodbc.dll in my program directory, but now i can't connect to DB, with default version program connect to DB normally. I use mingw32-bit for make dll.

            JonBJ 1 Reply Last reply
            0
            • J John Doe1

              @hskoglund, Hi, thanks for answer. Of course, every query i set query.setForwardOnly(true);
              before call query.exec(); in qsql_odbc.cpp i see "static const int COLNAMESIZE = 256;"
              i think that COLNAMESIZE look like OCI_ATTR_PREFETCH_ROWS for QOCI. I try to change COLNAMESIZE in qsql_odbc.cpp from 256 to 1024, and rebuild dll from source code. I rebuild dll, copy this new dll qsqlodbc.dll in my program directory, but now i can't connect to DB, with default version program connect to DB normally. I use mingw32-bit for make dll.

              JonBJ Offline
              JonBJ Offline
              JonB
              wrote on last edited by JonB
              #6

              @John-Doe1 said in FetchMore with ODBC driver extremely slow:

              Of course, every query i set query.setForwardOnly(true);

              You would be surprised at how many people do not.

              i think that COLNAMESIZE look like OCI_ATTR_PREFETCH_ROWS for QOCI

              I think it is for the maximum number of characters allowed in a column name!

              J 1 Reply Last reply
              0
              • JonBJ JonB

                @John-Doe1 said in FetchMore with ODBC driver extremely slow:

                Of course, every query i set query.setForwardOnly(true);

                You would be surprised at how many people do not.

                i think that COLNAMESIZE look like OCI_ATTR_PREFETCH_ROWS for QOCI

                I think it is for the maximum number of characters allowed in a column name!

                J Offline
                J Offline
                John Doe1
                wrote on last edited by
                #7

                @JonB Thanks. I think that i need to use other driver, not QODBC, or explore qsql_odbc.cpp more and fix this.

                1 Reply Last reply
                0

                • Login

                • Login or register to search.
                • First post
                  Last post
                0
                • Categories
                • Recent
                • Tags
                • Popular
                • Users
                • Groups
                • Search
                • Get Qt Extensions
                • Unsolved