Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    13

    Unanswered: Number of Rows in a Result Set

    Hello,

    Operating System: AIX v4.3.3.0
    DB2: v7.1.0 02010105

    I support a legacy application that uses the Call Level Interface library to insert/update/delete/select data from database tables. In one part of the application it executes a SELECT statement to open a cursor and then executes a COUNT(*) to determine the number of rows in the result set. To me this is double jeopardy and it is a big performance hit for me. Is there a way other than COUNT(*) to determine the number of rows in a result set?

    I've searched the forum and didn't find anything helpful.

    Thank you very much...

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, there is no other way.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    No, there is no other way.
    Exactly. DB2 can not know exactly how many rows a query will return. Even if you do a COUNT(*) first, this result is out-dated right away unless you use the very restrictive REPEATABLE READ/SERIALIZABLE isolation level.

    Usually, an application that needs the exact number of rows has a design problem. You should just run your query and fetch rows until you get SQLCODE +100.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    Usually, an application that needs the exact number of rows has a design problem. You should just run your query and fetch rows until you get SQLCODE +100.
    It is not unusual for an application to show how many web pages (or items) the user can browse through before they actually get to the end, and it usually does not have to be exact.

    One thing to remember is that at least when you start paging through the items for real, the smallest index is probably still in the bufferpool.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    It is not unusual for an application to show how many web pages (or items) the user can browse through before they actually get to the end, and it usually does not have to be exact.
    That's the point: the numbers are just estimates. I think we are fully in agreement here.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Sep 2007
    Posts
    13

    Number of Rows in Result Set

    Thank you all for your replies. You pretty much confirmed what I had already suspected. It's just that I thought that if you have an order by clause then DB2 should know how many rows are in the result set, shouldn't it? Or am missing something?

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 does not always have to read all the rows and sort them to satisfy an order by. It is sometimes possible to use index access in lieu of a sort and only read the number of rows fetched. Indexes are always in exact order and if that is the order you requested, then DB2 may not have to materialize the answer set to fetch the first n rows.

    It is true that when an answer set needs to be materialized in order to return the first row (because of a sort) then DB2 would know how many rows are in the answer set. But then you have the problem of getting two different results based on whether an index exists (or was dropped). This completely violates the principles of a relational database.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •