Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Posts
    8

    Unanswered: MQT Refresh painfully slow

    I have a MQT which reads data from three tables. The problem is refresh is terribly slow. Note that I have ensured indexes on all the base tables with their statistics updated.

    Can anything be done to expedite the refresh?

    Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can take the query SQL of the MQT definition and explain it. Maybe that will help.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by diwakarmdba View Post
    refresh is terribly slow.
    Is that a new measurement unit? And why is that a problem?

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    You can try temporary disabling logging during refresh table, check this technote for more info:
    IBM - How to temporarily turn logging off for operations modifying the table?

    But if some error occurs during the not logged operation, the MQT will be marked as inaccessible.

  5. #5
    Join Date
    Apr 2010
    Posts
    8

    Thumbs down bactiffi consular

    I did an explain on the MQT and it had a paltry cost of 714 timerons but actually if I run the query using db2batch it takes a whopping 723 seconds.

    I did try db2advis on the query within the MQT but it did not recommend any indexes as the existing indexes would suffice.

    I checked if any of the base tables needed reorganization but it was not the case.

    Any inputs on the top of what I have done thus far would be appreciated. Thanks

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You could post the SQL for suggestions on how to write it differently. We might be able to help out with something there, but your refresh is going to be as slow or as fast as the query that is being executed can return the rows.
    Dave

Posting Permissions

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