Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41

    Unanswered: slow fetch from temp table

    Hello

    On DB2 9.1.8 i test some query with fullscan of 21M rows (part of some ETL process)
    I start this query with db2batch and check "db2top" table's snapshot.
    I see what Delta Rows Read/s is ~ 60000 with the same Delta Rows Written/s to temp table. But after temp table is filled then fetch process from temp table is only ~4000 rows/s
    Temp space is SMS with 16K page size and EXTENTSIZE=2
    No other concurrent temp tables reads.

    How to speed up fetch from temp table ?

    Thank's

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Check if tempspace has filesystem caching enabled. I was told that it can be beneficial for SMS tempspaces.

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2girl View Post
    I was told that it can be beneficial for SMS tempspaces.
    Strange . I was told that it is only beneficial for LOB tablespaces. I am going to follow this tread with extra interest now...

  4. #4
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41
    Quote Originally Posted by db2girl View Post
    Check if tempspace has filesystem caching enabled. I was told that it can be beneficial for SMS tempspaces.
    Thank you, but this not helped.
    Also, the test query have ORDER BY clause and when i disable this clause the test query not use temp table.
    But fetch speed from real table is the same ~4000 rows/s. (as from temp table)

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    dr_te_z, This is mentioned in the DB2 for AIX redbook and performance people suggested the same. Please see this thread for some additional info:
    http://www.dbforums.com/db2/1660885-...m-caching.html


    mitrofun, Are you just testing the performance of a table scan? Check snapshots for asynchronous data page reads vs. physical data page reads and time waited for prefetch. You can try changing prefetchsize, num_ioservers...

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Interesting stuff. I think you could include this Size of temporary bufferpool in DB2 | db2ude in the equation.
    When you, based upon the above article, trim your temp-space-bufferpools down you could decide to activate file system caching to compensate for the effect.

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by dr_te_z View Post
    From this article:
    "As a best practice, you should keep the size of your temporary buffer pool small if you have DB2 Stored Procedures creating and dropping temporary tables and these stored procedures are used very heavily. In this case, you are best left with a small buffer pool for that temporary table space."


    Does create/drop of temporary tables (does he mean global temporary tables or regular tables that exist for a short period of time?) use system temporary tablespace?

Posting Permissions

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