Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    Unanswered: sql optimization

    Hi people,
    I have to load some tables with more than 500 000 rows...
    How can i improve the performance and the run time....
    could i use the method of query parallelism to process the inserts statment in parallel?
    If is possible..how can i do that?
    Cursors..it's a good method or not?
    If you have more ideas to improve the performance, please tell me.

    Thanks,
    Rui

  2. #2
    Join Date
    Sep 2002
    Posts
    456

    Re: sql optimization

    if the input is in a flat file use load utility.

    dollar

    Originally posted by rmleite
    Hi people,
    I have to load some tables with more than 500 000 rows...
    How can i improve the performance and the run time....
    could i use the method of query parallelism to process the inserts statment in parallel?
    If is possible..how can i do that?
    Cursors..it's a good method or not?
    If you have more ideas to improve the performance, please tell me.

    Thanks,
    Rui

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: sql optimization

    You have provided very few details on what you want to do or your exact requirements/data format etc ...

    Assuming you have the data in a file in a db2 acceptable format, use the LOAD command ... 500000 rows should be in your table in a few minutes time ... If the data is in some other table, you can EXPORT from the source and LOAD into target ... If you are in V8, you can use a select statement instead of a file (but not sure how efficient it is ) ...

    Please provide more details if this answer does not suit your needs ...

    Read the 'Must Read before posting' thread in this forum to understand how you can make use of this forum for maximum benefit ....


    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    sql optimization

    This is the procedure that i will use to load 2 tables with 500 000 rows...
    ...the rows are come from one table and i don't know if i have to export to a flat file and load with the load utility or if i have to do like that:
    ..i think that i will take off "SET v_cont = v_cont + 1;
    IF v_cont > 5000 THEN
    COMMIT;
    SET v_cont = 0;
    END IF; "

    ... and put just one commit to the end...and so create the tables with the statment NOT LOGGED INITIALLY!!!


    CREATE PROCEDURE INT_SALE_E_SALE_MD ()
    SPECIFIC INT_SALE_E_SALE_MD
    LANGUAGE SQL
    ---------------------------------------------------------------
    -- SQL Stored Procedure
    ---------------------------------------------------------------
    P1: BEGIN
    -- Declaraçao de Variaveis

    DECLARE v_tpos_md_val DECIMAL;
    DECLARE v_clr_md_val DECIMAL;
    DECLARE v_tpos_md_val_eur DECIMAL;
    DECLARE v_clr_md_val_eur DECIMAL;
    DECLARE v_str_id SMALLINT;
    DECLARE v_yr_wk_id INTEGER;
    DECLARE v_sku_id INTEGER;
    DECLARE v_ts_val DECIMAL;
    DECLARE v_ts_qty INTEGER;
    DECLARE v_ts_val_eur DECIMAL;
    DECLARE v_base_ccy CHARACTER(3);
    DECLARE v_cont INTEGER DEFAULT 0;
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';


    -- declaração dos cursores

    DECLARE c1 CURSOR WITH HOLD FOR
    SELECT
    sl.tpos_md_val,
    sl.clr_md_val,
    sl.tpos_md_val_eur,
    sl.clr_md_val_eur
    FROM basedett.sls sl
    WHERE sl.co_id = 1
    FOR READ ONLY;

    DECLARE c2 CURSOR WITH HOLD FOR
    SELECT
    sl.str_id,
    sl.yr_wk_id,
    sl.sku_id,
    sl.ts_val,
    sl.ts_qty,
    sl.ts_val_eur,
    sl.base_ccy
    FROM basedett.sls sl
    WHERE sl.co_id = 1
    FOR READ ONLY;


    -- Exception Handlers
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;


    -- Criaçao da tabela SALE

    CREATE TABLE WORKTMPT.SALE ("SKU_ID" INTEGER , "STR_ID" SMALLINT ,
    "YR_WK_ID" INTEGER , "TS_QTY" INTEGER , "TS_VAL" DECIMAL (9, 0) ,
    "TS_VAL_EUR" DECIMAL (11, 0) , "FL_PRC_SLS_QTY" INTEGER , "FL_PRC_SLS_VAL" DECIMAL (9, 0) ,
    "FL_PRC_SLS_VAL_EUR" DECIMAL (11, 0) , "CLR_MD_SLS_QTY" INTEGER, "CLR_MD_SLS_VAL" DECIMAL(9,0),
    "CLR_MD_SLS_VAL_EUR" DECIMAL(11,0),"TPOS_SLS_QTY" INTEGER,"TPOS_SLS_VAL" DECIMAL (9,0),
    "TPOS_SLS_VAL_EUR" DECIMAL (11,0),"TS_XTX_VAL" DECIMAL(9,0),"TS_XTX_VAL_EUR" DECIMAL(11,0),
    "BASE_CCY" CHARACTER(3),"WK_FIRST_SOLD" INTEGER, "ROW_STATUS" CHARACTER(1), "RATE_OF_SALE" DECIMAL(9,0),
    "AVAIL_FROM_DT" DATE)
    IN USERSPACE3;

    -- Criação da tabela SALE_MD

    CREATE TABLE WORKTMPT.SALE_MD(
    "SKU_ID" INTEGER ,
    "STR_ID" SMALLINT ,
    "YR_WK_ID" INTEGER,
    "CLR_MD_VAL" DECIMAL(9,2),
    "CLR_MD_VAL_EUR" DECIMAL(11,2),
    "PERM_MU_VAL" DECIMAL(9,2),
    "PERM_MU_VAL_EUR" DECIMAL(11,2),
    "NLP_MD_QTY" SMALLINT,
    "NLP_MD_VAL" DECIMAL(9,2),
    "NLP_MD_VAL_EUR" DECIMAL(11,2),
    "POS_MD_QTY" INTEGER,
    "POS_MD_VAL" DECIMAL(9,2),
    "POS_MD_VAL_EUR" DECIMAL(11,2),
    "NLP_ADJ_VAL" DECIMAL(9,2),
    "NLP_ADJ_VAL_EUR" DECIMAL(11,2),
    "TPOS_MD_QTY" INTEGER,
    "TPOS_MD_VAL" DECIMAL(9,2),
    "TPOS_MD_VAL_EUR" DECIMAL(11,2),
    "TOT_MD_QTY" INTEGER,
    "TOT_MD_VAL" DECIMAL(9,2),
    "TOT_MD_VAL_EUR" DECIMAL(11,2),
    "VCHR_MD_QTY" SMALLINT,
    "VCHR_MD_VAL" DECIMAL(9,2),
    "VCHR_MD_VAL_EUR" DECIMAL(11,2),
    "TOT_MD_XTX_VAL" DECIMAL(9,2),
    "TOT_MD_XTX_VAL_EUR" DECIMAL(11,2),
    "POS_EQV_MD_VAL" DECIMAL(9,2),
    "POS_EQV_MD_VAL_EUR" DECIMAL(11,2),
    "LCL_MD_QTY" INTEGER ,
    "LCL_MD_VAL" DECIMAL(9,2),
    "LCL_MD_VAL_EUR" DECIMAL(11,2),
    "DMG_ALW_QTY" SMALLINT,
    "DMG_ALW_VAL" DECIMAL(9,2) ,
    "DMG_ALW_VAL_EUR" DECIMAL(11,2) ,
    "DMG_WO_QTY" SMALLINT ,
    "DMG_WO_VAL" DECIMAL(9,2) ,
    "DMG_WO_VAL_EUR" DECIMAL(11,2) ,
    "FLTY_ALW_QTY" SMALLINT ,
    "FLTY_ALW_VAL" DECIMAL(9,2) ,
    "FLTY_ALW_VAL_EUR" DECIMAL(11,2) ,
    "FLTY_WO_QTY" SMALLINT ,
    "FLTY_WO_VAL" DECIMAL(9,2) ,
    "FLTY_WO_VAL_EUR" DECIMAL(11,2) ,
    "GDWL_ALW_QTY" SMALLINT ,
    "GDWL_ALW_VAL" DECIMAL(9,2) ,
    "GDWL_ALW_VAL_EUR" DECIMAL(11,2) ,
    "GDWL_WO_QTY" SMALLINT ,
    "GDWL_WO_VAL" DECIMAL(9,2) ,
    "GDWL_WO_VAL_EUR" DECIMAL(11,2) ,
    "SYS_STK_ADJ_VAL" DECIMAL(9,2) ,
    "SYS_STK_ADJ_VAL_EUR" DECIMAL(11,2) ,
    "FLTY_QTY" SMALLINT ,
    "FLTY_VAL" DECIMAL(9,2) ,
    "FLTY_VAL_EUR" DECIMAL(11,2) ,
    "GDWL_QTY" SMALLINT ,
    "GDWL_VAL" DECIMAL(9,2) ,
    "GDWL_VAL_EUR" DECIMAL(11,2) ,
    "DMG_QTY" SMALLINT ,
    "DMG_VAL" DECIMAL(9,2) ,
    "DMG_VAL_EUR" DECIMAL(11,2) ,
    "FDG_QTY" SMALLINT ,
    "FDG_VAL" DECIMAL(9,2) ,
    "FDG_VAL_EUR" DECIMAL(11,2) ,
    "BASE_CCY" CHAR(3) ,
    "ROW_STATUS" CHAR(1) ,
    "AVAIL_FROM_DT" DATE)
    IN USERSPACE1 ;

    -- Program Core
    -- Inserir dados da tabela BASEDETT.SLS para a tabela WORKTMPT.SALE_MD

    OPEN c1;
    FETCH c1 INTO v_sku_id,v_str_id,v_tpos_md_val, v_clr_md_val, v_tpos_md_val_eur,
    v_clr_md_val_eur;
    WHILE at_end = 0 DO
    INSERT INTO worktmpt.sale_md
    VALUES (1,222,222,v_clr_md_val,v_clr_md_val_eur,444,
    444,444,444,444,444,444,444,444,444,444,v_tpos_md_ val,
    v_tpos_md_val_eur,444,444,444,444,444,
    444,444,444,444,444,
    444,444,444,444,444,444,444,
    444,0,0,444,444,444,444,0,0,
    444,444,444,444,0,0,444,
    444,444,444,444,444,444,444,
    444,444,444,444,444,'gbp','p','2003-12-10');
    SET v_cont = v_cont + 1;
    IF v_cont > 5000 THEN
    COMMIT;
    SET v_cont = 0;
    END IF;
    FETCH c1 INTO v_tpos_md_val, v_clr_md_val, v_tpos_md_val_eur, v_clr_md_val_eur;
    END WHILE;
    CLOSE c1;
    COMMIT;
    SET at_end = 0;

    -- Inserir dados da tabela BASEDETT.SLS para a tabela WORKTMPT.SALE
    OPEN c2;
    FETCH c2 INTO v_str_id,v_yr_wk_id,v_sku_id,v_ts_val,v_ts_qty,v_t s_val_eur,v_base_ccy;
    WHILE at_end = 0 DO
    INSERT INTO worktmpt.sale
    VALUES(v_sku_id,v_str_id,333,v_ts_qty,v_ts_val,v_t s_val_eur,999,999,999,999,999,999,999,
    999,999,999,999,v_base_ccy,999,'u',999,'2003-12-10');
    SET v_cont = v_cont + 1;
    IF v_cont > 5000 THEN
    COMMIT;
    SET v_cont = 0;
    END IF;
    FETCH c2 INTO v_str_id,v_yr_wk_id,v_sku_id,v_ts_val,v_ts_qty,v_t s_val_eur,v_base_ccy;
    END WHILE;
    CLOSE c2;

    DELETE FROM BASEDETT.SLS;
    COMMIT;
    END P1

Posting Permissions

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