Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    67

    Unanswered: Fastest method to know the number of records from a select

    Windows Server 2008
    DB2 WSE 9.7.6

    What is the fastest method to know the number of records from a select?
    1.
    Code:
    SELECT COUNT(*) FROM TABLE WHERE condition
    2.
    Code:
    EXPORT TO FILE.TXT OF DEL MODIFIED BY COLDEL; SELECT * FROM TABLE WHERE condition
    and searching for the number of exported rows

    3 Other?

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by CCMF View Post
    Windows Server 2008
    DB2 WSE 9.7.6

    What is the fastest method to know the number of records from a select?
    1.
    Code:
    SELECT COUNT(*) FROM TABLE WHERE condition
    2.
    Code:
    EXPORT TO FILE.TXT OF DEL MODIFIED BY COLDEL; SELECT * FROM TABLE WHERE condition
    and searching for the number of exported rows

    3 Other?

    2 is going to be magnitudes slower than 1, that's for sure.

    if all you want is an estimate, you can use a sample, Example:

    Code:
    select count(1) from ... tablesample system(1) where ...
    There's also a bernouilli sample that is a bit slower but more accurate
    --
    Lennart

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Quick return question. Why would it matter? You are either going to give all that are there or some subset, if you want to cut off after the first n rows. Other than commit counters on very large cursors never understood the wanting to know.
    Dave

  4. #4
    Join Date
    Nov 2004
    Posts
    67
    I have to control records in two databases, one local and one remote.

    It seemed to me that a count at the local database needed more time than an export.

    To test that I wrote two scripts:

    Script I:
    Local database:
    db2 select current timestamp from sysibm.sysdummy1
    db2 "select count(*) from ... where ..."
    db2 select current timestamp from sysibm.sysdummy1
    db2 "export to ... " | egrep -i number
    db2 select current timestamp from sysibm.sysdummy1

    Remote database:
    db2 select current timestamp from sysibm.sysdummy1
    db2 "select count(*) from ... where ..."
    db2 select current timestamp from sysibm.sysdummy1
    db2 "export to ... " | egrep -i number
    db2 select current timestamp from sysibm.sysdummy1


    Script II:
    Local database:
    db2 select current timestamp from sysibm.sysdummy1
    db2 "export to ... " | egrep -i number
    db2 select current timestamp from sysibm.sysdummy1
    db2 "select count(*) from ... where ..."
    db2 select current timestamp from sysibm.sysdummy1

    Remote database:
    db2 select current timestamp from sysibm.sysdummy1
    db2 "export to ... " | egrep -i number
    db2 select current timestamp from sysibm.sysdummy1
    db2 "select count(*) from ... where ..."
    db2 select current timestamp from sysibm.sysdummy1

    Results after some tests:
    For local database count and export take about the same time.
    For remote database count is faster than export.


    Thank you for your comments. This thread can be closed.

Posting Permissions

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