CODEWITHSUNDEEP
db2sql-insert
user3376274
user3376274

Reputation: 51

DB2 SQL inserts are very slow when table has a primary key

I have a DB2 table (map_card_number) that takes 5 minutes to insert 1M rows of data when it has a primary key, compared to 11 seconds to insert the same 1M rows of data when the primary key is removed.

Here are the details.

  1. The SQL create table has the following syntax without the primary key:
DROP TABLE IF EXISTS MAP_CARD_NUMBER;
CREATE TABLE MAP_CARD_NUMBER
    (
        KEY_OF_MAP   VARCHAR(255) NOT NULL,
        VALUE_OF_MAP VARCHAR(1024) NOT NULL,
        UNIQUE (KEY_OF_MAP)
    );
  1. The SQL create table has the following syntax with the primary key:
DROP TABLE IF EXISTS MAP_CARD_NUMBER;
CREATE TABLE MAP_CARD_NUMBER
    (
        ID  INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        KEY_OF_MAP   VARCHAR(255) NOT NULL,
        VALUE_OF_MAP VARCHAR(1024) NOT NULL,
        PRIMARY KEY (ID),
        UNIQUE (KEY_OF_MAP)
    );

I also have two stored procedures that handle populating the map_card_number table.

  1. The first stored procedure selects from a customer table that contains 1M rows, creates a cursor, traverses each row, one at a time, replaces '41000' with '32888' of the card_number, and calls the second stored procedure to save '41000' as the key and '32888' as the value to the map_card_number table.
CREATE OR REPLACE PROCEDURE POPULATE_CC_MAP()
    LANGUAGE SQL
BEGIN
    DECLARE v_card_number VARCHAR(25);
    DECLARE v_value VARCHAR(4000);
    DECLARE v_counter INT DEFAULT 0;

    FOR row AS cur1 CURSOR WITH HOLD FOR
        SELECT card_number
        FROM customer
        ORDER BY id
        DO
            SET v_counter = v_counter + 1;          
            SET v_card_number = row.card_number;
            SET v_value = REPLACE(v_card_number,'41000','32888');

            CALL map_put_card_number(v_card_number, v_value);      
           
            IF v_counter = 10000 THEN
              COMMIT;
              SET v_counter = 0;
            END IF;  

        END FOR;
END;
  1. The second stored procedure saves the key and the value to the map table.
CREATE OR REPLACE PROCEDURE MAP_PUT_CARD_NUMBER(IN p_key VARCHAR(255), IN p_value VARCHAR(1024))
BEGIN
    MERGE INTO map_card_number AS SOURCE
    USING (VALUES (p_key, p_value)) AS merge (key_of_map, value_of_map)
    ON SOURCE.KEY_OF_MAP = merge.key_of_map
    WHEN NOT MATCHED THEN
        INSERT (key_of_map, value_of_map)
        VALUES (p_key, p_value);
END;

So, here are my questions:

  1. Why is inserting drastically slower when the primary key is added than when a primary key is not added?
  2. What corrections need to be made so that inserts with a primary key are nearly as fast as inserting without a primary key?

Thank you, in advance, to anyone who provides suggestions to help me resolve this challenge.

Upvotes: 0

Views: 119

Answers (0)

Related Questions