Professional Web Applications Themes

select count(*) from <table> taking FOREVER - IBM DB2

Is it a single db partition or multiple db partitions db server? If it is EE, you can turn on INTRA_PARALLELISM. Regards, FRX [email]allenjndr.com[/email] wrote: > DB2 v8 FP2 > Red Hat Linux 7.3 > > I have a large (well, I think its large) <table> containing around 100 > million rows. > > When I do a > select count(*) from <table> > either from JDBC or from the DB2 CLP, things just hang. It never > comes back. Any other queries against the table are pronto, so its > not a locked table or anything. I have 2 ...

  1. #1

    Default Re: select count(*) from <table> taking FOREVER

    Is it a single db partition or multiple db partitions db server?
    If it is EE, you can turn on INTRA_PARALLELISM.
    Regards,
    FRX

    [email]allenjndr.com[/email] wrote:
    > DB2 v8 FP2
    > Red Hat Linux 7.3
    >
    > I have a large (well, I think its large) <table> containing around 100
    > million rows.
    >
    > When I do a
    > select count(*) from <table>
    > either from JDBC or from the DB2 CLP, things just hang. It never
    > comes back. Any other queries against the table are pronto, so its
    > not a locked table or anything. I have 2 unique indexes against
    > the table.
    >
    > I have run stats against the table a LOT...
    >
    > This should be instant. Any idea whats happening?
    >
    > thanks
    >
    > Allen
    Fan Ruo Xin Guest

  2. #2

    Default Re: select count(*) from <table> taking FOREVER

    On Thu, 21 Aug 2003 21:45:53 +0000, allen wrote:
    > DB2 v8 FP2
    > Red Hat Linux 7.3
    >
    > I have a large (well, I think its large) <table> containing around 100
    > million rows.
    >
    > When I do a
    > select count(*) from <table>
    > either from JDBC or from the DB2 CLP, things just hang. It never
    > comes back. Any other queries against the table are pronto, so its
    > not a locked table or anything. I have 2 unique indexes against
    > the table.
    >
    > I have run stats against the table a LOT...
    >
    > This should be instant. Any idea whats happening?
    >
    > thanks
    >
    > Allen
    Allen,

    I'm not surprised it's taking some time : you're having to read through
    the whole table to return a count of all the rows. All you can do is try
    to make the count go as fast as possible by throwing as much hardware and
    parallelism at it as possible.

    I assume you're not considering such as statement for production ? If you
    were then maybe time to reconsider !!!

    If so, do you really need the count 100% accurate. If not, then pick up
    the ballpark figure from the catalog (CARDF on SYSIBM.SYSTABLES I think).
    This of course assumes that you've run RUNSTATS recently.

    HTH

    Phil
    Philip Nelson Guest

  3. #3

    Default Re: select count(*) from <table> taking FOREVER

    I don't think a table scan is needed. It's suffient to scan any index
    (ideally the smallest)

    Cheers
    Serge


    Serge Rielau Guest

  4. #4

    Default Re: select count(*) from <table> taking FOREVER

    On Fri, 22 Aug 2003 12:54:39 GMT, Philip Nelson <teamdbascotdb.com>
    wrote:
    >On Thu, 21 Aug 2003 21:45:53 +0000, allen wrote:
    >
    >> DB2 v8 FP2
    >> Red Hat Linux 7.3
    >>
    >> I have a large (well, I think its large) <table> containing around 100
    >> million rows.
    >>
    >> When I do a
    >> select count(*) from <table>
    >>I'm not surprised it's taking some time : you're having to read through
    >the whole table to return a count of all the rows. All you can do is try
    >to make the count go as fast as possible by throwing as much hardware and
    >parallelism at it as possible.
    >
    >I assume you're not considering such as statement for production ? If you
    >were then maybe time to reconsider !!!
    >
    >If so, do you really need the count 100% accurate. If not, then pick up
    >the ballpark figure from the catalog (CARDF on SYSIBM.SYSTABLES I think).
    >This of course assumes that you've run RUNSTATS recently.
    Shouldn't the "select count(*)" pick up the ballpark figure from the
    catalog, rather than literally counting rows?
    allenj@ndr.com Guest

  5. #5

    Default Re: select count(*) from <table> taking FOREVER

    I'm doing count(*) queries over multi-million rows tables with response
    times of a few seconds. For 100M rows, I'd expect around 1-2 minutes on our
    system (W2K AS, 4x500MHz Xeon, 2.75GB RAM). There's no I/O involved.

    You should examine DB2 I/O (use the performance monitoring tools). If it's
    doing I/O to count the rows, that's the problem. Add RAM and increase the
    buffer pool until it stops doing I/O (ours is around 1GB). Also enable
    multi-page prefretching, add I/O agents, etc, to speed up the initial I/O.

    If that doesn't help, you can always migrate from Linux to Windows 2000 /
    2003 AS or DC - it has a better I/O model. (Now you can all flame me!)

    <allenjndr.com> wrote in message
    news:3f453c83.1140763921news.supernews.com...
    > DB2 v8 FP2
    > Red Hat Linux 7.3
    >
    > I have a large (well, I think its large) <table> containing around 100
    > million rows.
    >
    > When I do a
    > select count(*) from <table>
    > either from JDBC or from the DB2 CLP, things just hang. It never
    > comes back. Any other queries against the table are pronto, so its
    > not a locked table or anything. I have 2 unique indexes against
    > the table.
    >
    > I have run stats against the table a LOT...
    >
    > This should be instant. Any idea whats happening?
    >
    > thanks
    >
    > Allen

    Mark Yudkin Guest

  6. #6

    Default Re: select count(*) from <table> taking FOREVER

    Only if you do db2set DB2_THUMB_RULE = YES;
    *tongue firmly in cheek*

    Cheers
    Serge


    Serge Rielau Guest

  7. #7

    Default Re: select count(*) from <table> taking FOREVER

    >I don't think a table scan is needed. It's suffient to scan any index
    >(ideally the smallest)
    agreed - you could verify this with explain
    MCPHEAL Guest

  8. #8

    Default Re: select count(*) from <table> taking FOREVER

    On Fri, 22 Aug 2003 13:53:26 -0400, Serge Rielau wrote:
    > Only if you do db2set DB2_THUMB_RULE = YES;
    > *tongue firmly in cheek*
    >
    > Cheers
    > Serge
    Would it not be possible to do something like this with the dynamic
    statistics in DB2 for z/OS V8 ?

    Just a thought.
    Philip Nelson Guest

  9. #9

    Default Re: select count(*) from <table> taking FOREVER

    count(*) is, if possible, modified by the optimizer with a column index.
    (index scan)

    SQL Statement:

    select count(*)
    from dboxs.lineitem
    for read only


    Section Code Page = 1252

    Estimated Cost = 57986,378906
    Estimated Cardinality = 1,000000

    Access Table Name = DBOXS.LINEITEM ID = 6,11
    | #Columns = 0
    | Relation Scan
    | | Prefetch: Eligible
    | Lock Intents
    | | Table: Intent Share
    | | Row : Next Key Share
    | Predicate Aggregation
    | | Column Function(s)
    Aggregation Completion
    | Column Function(s)
    Return Data to Application
    | #Columns = 1

    End of section


    create index
    reorg
    runstats
    explain

    SQL Statement:

    select count(*)
    from dboxs.lineitem
    for read only


    Section Code Page = 1252

    Estimated Cost = 38675,507813
    Estimated Cardinality = 1,000000

    Access Table Name = DBOXS.LINEITEM ID = 6,11
    | #Columns = 0
    | Index Scan: Name = DBOXS.X01LINEITEM ID = 1
    | | Regular Index (Clustered)
    | | Index Columns:
    | | | 1: L_ORDERKEY (Ascending)
    | | #Key Columns = 0
    | | | Start Key: Beginning of Index
    | | | Stop Key: End of Index
    | | Index-Only Access
    | | Index Prefetch: Eligible 1412
    | | Predicate Aggregation
    | | | Column Function(s)
    | Lock Intents
    | | Table: Intent Share
    | | Row : Next Key Share
    Aggregation Completion
    | Column Function(s)
    Return Data to Application
    | #Columns = 1

    End of section



    PM


    PM \(pm3iinc-nospam\) Guest

  10. #10

    Default Re: select count(*) from <table> taking FOREVER

    <allenjndr.com> wrote in message news:3f453c83.1140763921news.supernews.com...
    > DB2 v8 FP2
    > Red Hat Linux 7.3
    >
    > I have a large (well, I think its large) <table> containing around 100
    > million rows.
    >
    > When I do a
    > select count(*) from <table>
    > either from JDBC or from the DB2 CLP, things just hang. It never
    > comes back. Any other queries against the table are pronto, so its
    > not a locked table or anything. I have 2 unique indexes against
    > the table.
    You could use an summary table to hold the total count (or some partitioned count)


    Otherwise, in V8 this will give you some estimate of the number of rows

    db2 select count(*) * 100 from <table> tablesample system(1)



    Regards
    Paul Vernon
    Business Intelligence, IBM Global Services


    Paul Vernon Guest

  11. #11

    Default Re: select count(*) from <table> taking FOREVER

    use select count_big (*) from your_table
    Unregistered Guest

Similar Threads

  1. Movies taking forever to load
    By flashuser5555 in forum Macromedia Flash Player
    Replies: 0
    Last Post: November 25th, 07:31 PM
  2. ai files taking forever to pdf (IND CS2)
    By Kain_Glover@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 5
    Last Post: October 5th, 10:46 PM
  3. PDF taking forever to print?
    By kristin_maling@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 0
    Last Post: August 2nd, 08:31 PM
  4. Taking FOREVER to save a file
    By Murray *TMM* in forum Macromedia Dreamweaver
    Replies: 4
    Last Post: July 17th, 07:32 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139