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

    Unanswered: sql optimization

    hI PEOPLE,

    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!!!
    What do you think?
    Do you think that cursors are a good method?


    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

  2. #2
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186

    Re: sql optimization

    If your going to do a load, then just the not logged initially nonrecoverable. This will leave the tablespace in a use-able state when your finished. You should run a back up of this tablespsace as soon as your done. before you connect any applications too it. If you can use a flat file, that will be the quickest way to do it.

  3. #3
    Join Date
    Dec 2002
    Posts
    134

    Re: sql optimization

    Originally posted by quigleyd
    If your going to do a load, then just the not logged initially nonrecoverable. This will leave the tablespace in a use-able state when your finished. You should run a back up of this tablespsace as soon as your done. before you connect any applications too it. If you can use a flat file, that will be the quickest way to do it.
    If you are running in logretain mode (and you should if you are in production), there is a "copy to" option in load command. It allows to specify where to put a snapshot of loaded data (there is no need for tablespace level backup after the load)

    Also, if you are using v8, I think I've seen something like load from cursor

    regards,
    dmitri

Posting Permissions

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