Results 1 to 5 of 5

Thread: Insert is slow

  1. #1
    Join Date
    Sep 2003
    Location
    Bangalore
    Posts
    5

    Unanswered: Insert is slow

    Hi,
    I am trying to insert a record in a table. Table does not have index, trigger or check constrint.

    Following is my explain plan for a singel record insert which take almost 2 secods for me. Can anybdy suggest how to improve this?


    Sample of explain plan is pasted below.

    Thanks
    Ads




    Isolation Level = Cursor Stability
    Blocking = Block Unambiguous Cursors
    Query Optimization Class = 5

    Partition Parallel = Yes
    Intra-Partition Parallel = No

    SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "BCSUSR"


    SQL Statement:

    Insert into STG_DIM_LOB(v_rc_code, fic_mis_date, v_rc_desc,
    v_lvl18_resp_node, v_lvl18_resp_name, v_lvl20_resp_node,
    v_lvl20_resp_name, v_lvl22_resp_node, v_lvl22_resp_name,
    v_lvl24_resp_node, v_lvl24_resp_name, v_lvl28_resp_node,
    v_lvl28_resp_name, v_lvl35_resp_node, v_lvl35_resp_name,
    v_lvl42_resp_node, v_lvl42_resp_name, v_lvl44_resp_node,
    v_lvl44_resp_name, v_lvl46_resp_node, v_lvl46_resp_name,
    v_lvl48_resp_node, v_lvl48_resp_name, v_lvl54_resp_node,
    v_lvl54_resp_name, v_lvl56_resp_node, v_lvl56_resp_name,
    v_lvl58_resp_node, v_lvl58_resp_name, v_lvl60_resp_node,
    v_lvl60_resp_name, v_lvl84_resp_node, v_lvl84_resp_name,
    v_lvl86_resp_node, v_lvl86_resp_name, v_lvl90_resp_node,
    v_lvl90_resp_name, n_resp_bod_dim_key, v_geo_code,
    v_geo_desc, d_record_start_date, d_record_end_date,
    f_latest_record_indicator, v_bod_node_summary_lvl_name,
    v_bmo_buss_unit_name, v_bank_group_acronym,
    v_bod_node_src_code)Values('2', TODATE('20060331', '
    YYYYMMDD'), 'WINDSOR MAIN ONTARIO', null, null, null, null,
    null, null, null, null, null, null, 'U7312', 'WINDSOR M/O
    CONS', 'U7364', 'WINDSOR AREA', 'U7035', 'WINDSOR DISTRICT',
    'U7002', 'ONTARIO DIVISION', 'U7000', 'SUM OF 5 DIVISIONS','
    V0076', 'PHISICAL DISTR DIV - CONS', 'U0030', 'PCFS MARKET (
    @ TERM MTR)', 'U7900', 'P&C CANADA', 'U0001', 'PERS&COMM
    CUSTOMER GROUP(PCCG)', '99990', 'OPERATING COMPANIES', '
    K9898', 'TOTAL BANK TEB', '99995', 'TOTAL BANK COST CENTRES'
    , 0, 'CA', 'CANADA', null, null, null, 'PCCG Canada', null,'
    PCCG', 'I')


    Section Code Page = 819

    Estimated Cost = 70.808212
    Estimated Cardinality = 0.083333

    Coordinator Subsection - Main Processing:
    Distribute Subsection #2
    | Directed to Local Coordinator Node
    Distribute Subsection #1
    | Broadcast to Node List
    | | Nodes = 1, 2, 3, 4

    Subsection #1:
    Access Table Queue ID = q1 #Columns = 47
    Insert: Table Name = BCSUSR.STG_DIM_LOB ID = 14,140

    Subsection #2:
    Table Constructor
    | 1-Row(s)
    Residual Predicate(s)
    | #Predicates = 1
    | Conditional Evaluation
    | | Condition #1:
    | | | #Predicates = 1
    | | Condition #2:
    | | | #Predicates = 1
    | | | Table Constructor
    | | | | 1-Row(s)
    | | | ANY/ALL Subquery
    | | | | Access Table Constructor
    | | Condition #3:
    | | | #Predicates = 1
    | | Condition #4:
    | | | #Predicates = 1
    | | | Table Constructor
    | | | | 1-Row(s)
    | | | ANY/ALL Subquery
    | | | | Access Table Constructor
    | | Condition #5:
    | | | #Predicates = 1
    | | Condition #6:
    | | | #Predicates = 1
    | | | Table Constructor
    | | | | 1-Row(s)
    | | | ANY/ALL Subquery
    | | | | Access Table Constructor
    | | Condition #7:
    | | | #Predicates = 1
    | | Condition #8:
    | | | #Predicates = 1
    | | | Table Constructor
    | | | | 1-Row(s)
    | | | ANY/ALL Subquery
    | | | | Access Table Constructor
    | | Condition #9:
    | | | #Predicates = 1
    | | Condition #10:
    | | | #Predicates = 1
    | | | Table Constructor
    | | | | 1-Row(s)
    | | | ANY/ALL Subquery
    | | | | Access Table Constructor
    | | Condition #11:
    | | | #Predicates = 1
    | | Condition #12:
    | | | #Predicates = 1
    | | | Data Stream 1:
    | | | | Correlated
    | | | | Piped
    | | | | Table Constructor
    | | | | | 1-Row(s)
    | | | | Residual Predicate(s)
    | | | | | #Predicates = 1
    | | | | Nested Loop Join
    | | | | | Data Stream 2:
    | | | | | | Correlated
    | | | | | | Not Piped
    | | | | | | Table Constructor
    | | | | | | | 1-Row(s)
    | | | | | | Insert Into Temp Table ID = t1
    | | | | | | | #Columns = 0
    | | | | | End of Data Stream 2
    | | | | | Access Temp Table ID = t1
    | | | | | | #Columns = 0
    | | | | | | Single Record
    | | | | | | Relation Scan
    | | | | | | | Prefetch: 1 Pages
    | | | | Nested Loop Join
    | | | | | Piped Inner
    | | | | | Table Constructor
    | | | | | | 1-Row(s)
    | | | | | Residual Predicate(s)
    | | | | | | #Predicates = 1
    | | | | | | Conditional Evaluation
    | | | | | | | Condition #1:
    | | | | | | | | #Predicates = 1
    | | | | | | | Condition #2:
    | | | | | | | | #Predicates = 1
    | | | | | | | | Data Stream 3:
    | | | | | | | | | Correlated
    | | | | | | | | | Piped
    | | | | | | | | | Table Constructor
    | | | | | | | | | | 1-Row(s)
    | | | | | | | | | Nested Loop Join
    | | | | | | | | | | Piped Inner
    | | | | | | | | | | Table Constructor
    | | | | | | | | | | | 1-Row(s)
    | | | | | | | | | | Residual Predicate(s)
    | | | | | | | | | | | #Predicates = 1
    | | | | | | | | | | Nested Loop Join
    | | | | | | | | | | | Piped Inner
    | | | | | | | | | | | Table Constructor
    | | | | | | | | | | | | 1-Row(s)

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Estimate cost of 70.808212 is quite fast, usually .05 seconds or faster elapsed time inside DB2. You can do a snaphot for dynamic SQL to verify the elpased time.

    Are you submitting this from a remote client? Is the client already connected to the database? More information is needed on exactly how you submit the insert statement.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2003
    Location
    Bangalore
    Posts
    5
    hi,
    Thanks for replying.
    I am pretty much new to DB2. I have a bunch of such (like the one shown in the plan) insert statements to be exeted which are built dynamically and fired on the database as batch. we have a multi partitioned database.
    My DBA provided me the logs of these statement fired on the database, and I can see that each statement execution time is more than a second. Hence I tried to execute the statement on my db2 client (Command promt). Again I can see that it takes alomost same amount of time.

    Yes, These statements fired from remote client. And client is already connected.

    Thanks again
    Ads
    Ads

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I need to see the "log" that you got from your DBA that shows it took 1 second for the insert. I suspect that much of the 1 second you are seeing is in the network, and not in DB2, but I would need to see a snapshot for dyanmic SQL to determine that.

    DB2 partitioning (hash partitioning with DPF) is designed for parallel operations, usually with data warehouses that have a lot of table scans needed to retrieve the data.

    However, you are only inserting one row at a time, and a single insert cannot be parallelized, so you should not expect good performance for this type of SQL with partitioning compared to a non-partitioned database. For best results with partitioning you should use batch inserts (multiple value clauses for each insert), or db2atld (the DB2 Autoloader which is described in the DB2 Command Reference manual).

    Also, there are a lot of other tuning factors that you have not provided that could be affecting performance.

    But I will make one suggestion: Change the value TODATE('20060331', '
    YYYYMMDD') to '2006-03-31' (that is all you need if the column is defined as a DB2 date).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It seems a lot more is going on there than meets the eye. If you look at the plan fragment included in the original message you'll notice that after the actual insert there's some stuff happening on the coordinator node that includes NL joins and application of predicates... Might me something related to the referential integrity constraints, or a bunch of MQT's are being refreshed, or something else...

Posting Permissions

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