Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Unanswered: Poor INSERT performance

    Hello,

    I have a trouble doing an insert from TEMP (global temporary table, on
    commit preserve rows, not logged) table into a permanent one:

    1. insert into PERM1 select * from TMP1 (about 250 000 rows):
    2. insert into PERM2 select * from TMP2 (about 300 000 rows)

    and the order of inserts does not matter

    Both tables are in SMS tablespace, have similar structure, no triggers
    or constraints (some stored procedures though), PERM1 has 5 indexes on
    it and PERM2 has 3. However the 1st insert takes about 15 minutes, but
    the second one - more than one hour.

    What could be the reason of such big difference? Could you advise how
    to investigate?

    Thanks!

    P.S.

    PERM1 table:
    Column
    Data type name Length Scale Nulls
    ------------------- ---------- ----- -----
    INTEGER 4 0 No
    INTEGER 4 0 Yes
    VARCHAR 100 0 Yes
    TIMESTAMP 10 0 No
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    VARCHAR 50 0 Yes
    VARCHAR 20 0 Yes
    VARCHAR 50 0 Yes
    VARCHAR 25 0 Yes
    VARCHAR 25 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    VARCHAR 30 0 Yes
    TIMESTAMP 10 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    CHARACTER 1 0 Yes
    BIGINT 8 0 Yes
    VARCHAR 15 0 Yes
    TIMESTAMP 10 0 Yes
    VARCHAR 40 0 Yes
    VARCHAR 10 0 Yes
    SMALLINT 2 0 Yes
    CHARACTER 1 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    TIMESTAMP 10 0 Yes
    TIMESTAMP 10 0 No
    VARCHAR 20 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes

    PERM2 table:
    Column
    Data type name Length Scale Nulls
    ------------------- ---------- ----- ------
    INTEGER 4 0 No
    TIMESTAMP 10 0 No
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    BIGINT 8 0 Yes
    INTEGER 4 0 Yes
    VARCHAR 100 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    VARCHAR 50 0 Yes
    VARCHAR 20 0 Yes
    VARCHAR 50 0 Yes
    INTEGER 4 0 Yes
    TIMESTAMP 10 0 Yes
    CHARACTER 1 0 Yes
    TIMESTAMP 10 0 Yes
    VARCHAR 40 0 Yes
    TIMESTAMP 10 0 No
    VARCHAR 20 0 Yes
    CHARACTER 1 0 Yes
    VARCHAR 10 0 Yes
    SMALLINT 2 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    TIMESTAMP 10 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes
    INTEGER 4 0 Yes

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Number of Indices and the key-size of each index is a contributing factor towards faster/slower inserts.

    As you are comparing the INSERT in case 1 (15 minutes) and case 2 (30 minutes +), if you are on test environment or have the flexibility to drop the indexes, I will recommend you to drop all the eight indexes from both the tables and check the duration taken for INSERT in both the cases. If the time taken is somewhat close to each other, you have a strong reason to believe that number of indexes and their size in both the tables is the main reason for the difference in the time ( 15 min Vs 30 min). Else, we need to look into remaining factors contributing for INSERT performance .

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  3. #3
    Join Date
    Oct 2007
    Posts
    246
    As jayanta suggested could be indexes which is causing the slow response could u
    please provide the type of indexes like is unique or any primary key defined and u can also try append on for the table which append ur insert at last

    regds
    Paul

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Are you executing both insert statements at the same time? Are tables PERM1 and PERM2 defined in the same SMS tablespace?

    Take a look at:
    Tips for improving INSERT performance in DB2 Universal Database

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    The SMS tablespaces are not helping in this case. Over and over again DB2 has to knock on the O.S. door for another extend so the file can grow. Is there enough free space on disk? Which file-system is used and is it fragmented? If it is feasible I'd use a DMS tablespace with a size already large enough to contain all the data. Spread the containers over as many spindles as possible.

  6. #6
    Join Date
    Nov 2009
    Posts
    3
    Thank you all for the replies, but this I've tried all of this before posting the question...

    • Index removal does not help

    • In test env. we have only one TS, all tables are sharing it. For the production system, all permanent tables are in DMS, the HDD has enough free space, so it's not the case here

    • And yes, I've studied this developerworks article (as well as many other sources), but I'm limited to INSERT only, since APPEND ON, LOAD will lead to REORG PENDING state for a table or BACKUP PENDING state for db, which is not acceptable (over 40 mln records in prod. table, the calculation is done daily), and besides the application user is quite restricted...

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use DB2 diagnostic tools (db2top, db2pd, snapshot monitor) to determine where your bottleneck is: I/O performance; page cleaning; logging; data maintenance (free space search, index page splits). When you know what the problem is you will be able to better address it.

  8. #8
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    The order of the tables could be a factor. Unless I'm mistaken, z/OS tables are physically stored in clustering sequence. If the one DGTT is close to the clustering sequence of its target, and the second is not close to the clustering sequence of its target, that might explain the elapsed time difference. I hope that you're not issuing a single INSERT for a unit of work that regularly exceeds an hour. If you're not using a cursor to retreive the rows from the DGTT for the INSERTS, try coding one with an ORDER BY that matches the clustering sequence of the INSERT target table. Make sure you use a reasonable commit frequency, 30, maybe 60 seconds.

  9. #9
    Join Date
    Nov 2009
    Posts
    3
    Unfortunately the order is not a factor - I've tried it already. And I'm on Linux, not Z/OS. The only significant difference between tables, I've found so far - is the PCTFREE value, but changing it did not help... And I do issue a single INSERT, but I don't use the transaction control, i.e. it's autocommitted and it is a single UOW. We do not see any problems with it, since it's not the OLTP environment. The problem is definitely in the table definition, because I've tried to switch the destination of INSERT and it worked fine...

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... The problem is definitely in the table definition, because ...
    If you sure of that and you want help on this forum,
    I think that you should show complete DDLs(at least both tables and all indexes).
    Although you showed all columns with data types, create table statement includes more parameters and options.

    However, you also wrote...
    Index removal does not help
    Did you tried and compared with dropping all indexes on both tables?
    Last edited by tonkuma; 02-16-11 at 10:38.

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, you mention:
    no triggers
    or constraints (some stored procedures though)
    What do you mean with the stored procedures? Also, what do you mean with:
    because I've tried to switch the destination of INSERT and it worked fine
    do you mean you performed the insert to another table, that is a duplicate of your table? If that is the case, I am guessing that it could be your new target table is empty. You mentioned playing with pctfree. After you changed it in your existing target, did you reorg the table? prior to attempting the inserts again.
    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
  •