Re: ACCELERATING STORE PROCEDURE

From: Ed Prochak <edprochak_at_gmail.com>
Date: Thu, 19 Feb 2009 12:22:06 -0800 (PST)
Message-ID: <cac6085b-09f9-486f-9562-7ccb46b70118_at_t11g2000yqg.googlegroups.com>



On Feb 19, 2:29 pm, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
> Hi everyone,
>
> Hoping you are ok, i need your help to solve this query in order to
> make faster an stored procedure, it goes likes this:
>
> Tables Sources -> SP procesing some data -> Tables Destiny (Results)
>
> So my source's tables are
>
> OM_DB.CDRS (86 fields and 30,000 rows average)
> OM_DB.COFETEL (8 fields and 62,000 rows average)
> OM_DB.CDRS_LISTED_ISDN_CAUSE_CODES (4 fields, 54 rows and this is a
> catalog, no index)
>
> Those source tables with this indexes
>
> CREATE INDEX PSA.CDRS_IX
> ON OM_DB.CDRS(START_TIME,CALLED_PARTY_ON_DEST,CALL_SOURCE_REGID)
>
> CREATE INDEX PSA.SX_CDRS
> ON OM_DB.CDRS(ISDN_CAUSE_CODE,CALL_DURATION_INT)
>
> CREATE INDEX PSA.COFETEL_IX
> ON OM_DB.COFETEL(MODALIDAD)
>
> CREATE INDEX PSA.COFETEL_SX
> ON OM_DB.COFETEL(NIR_INICIAL,NIR_FINAL)
>
> The destiny's tables are
>
> om_db.CDRS_FINAL1 (9 fields, 5 rows per hour)
> om_db.CDRS_ISDN_CAUSE_CODE (5 fields, 50 rows per hour)
>
> Those destiny tables with this indexes
>
> CREATE UNIQUE INDEX PSA.IX_CDRS_FINAL1
> ON OM_DB.CDRS_FINAL1(PMM_DATETIME,CALL_SOURCE_REGID)
>
> CREATE UNIQUE INDEX PSA.IX_CDRS_ISDN_CAUSE_CODE
> ON OM_DB.CDRS_ISDN_CAUSE_CODE
> (PMM_DATETIME,CALL_SOURCE_REGID,ISDN_CAUSE_CODE)
>
> I need run this procedure each 15 minutes and there are 30,000 rows
> average on source OM_DB.CDRS, the execution time often exceed 15
> minutes so i need to make better querys this is the sp:
>
> Note: The DISTINCT(CALL_SOURCE_REGID) is 5 to 10 rows.
>
> CREATE OR REPLACE PROCEDURE OM_DB.PR_MAKE_CDRS_FINAL1
> IS
>     vPMM_DATETIME        TIMESTAMP(3);
>     vMAX_PMM_DATETIME    TIMESTAMP(3);
>     vEND_DATETIME        TIMESTAMP(3);
>     vCALL_SOURCE_REGID   VARCHAR2(30);
>     vTOTAL_CDRS_CLIENTE  INT;
>     vMIN_VALID_COFETEL   INT;
>     vMIN_INVALID_COFETEL INT;
>     vMOVILES_VALIDOS     INT;
>     vMOVILES_INVALIDOS   INT;
>     vFIJOS_VALIDOS       INT;
>     vFIJOS_INVALIDOS     INT;
>     vNUMERIC_REASON_CODE INT;
>     vTOTAL_ISDN_CAUSE    INT;
>     vCALL_DURATION_INT   INT;
>     v_rows_processed     INT;
>     count_violated       INT;
>
>     CURSOR CL_CDR IS SELECT DISTINCT(CALL_SOURCE_REGID) FROM
> om_db.CDRS WHERE CALL_SOURCE_REGID IS NOT NULL;
>     CURSOR L_ISDN IS SELECT NUMERIC_REASON_CODE FROM
> om_db.CDRS_LISTED_ISDN_CAUSE_CODES ORDER BY NUMERIC_REASON_CODE;
>
> BEGIN
>     dbms_output.enable(40000);
>     dbms_output.put_line('Inicio de procedimiento: ' || TO_CHAR
> (sysdate, 'YYYY-MM-DD HH24:MI:SS'));
>
>     --  Valida si existe informacion en la tabla de origen
>     count_violated := 0;
>     SELECT COUNT(*) INTO count_violated FROM om_db.CDRS;
>     IF (count_violated=0) THEN
>         dbms_output.put_line('No hay informacion en la tabla de
> origen');
>     ELSE
>         SELECT TO_DATE(TO_CHAR(MIN(START_TIME),'YYYY-MM-DD HH24') || ':
> 00:00','YYYY-MM-DD HH24:MI:SS')
>         INTO vPMM_DATETIME FROM om_db.CDRS;
>         vEND_DATETIME := vPMM_DATETIME + 1/24;
>         SELECT TO_DATE(TO_CHAR(MAX(START_TIME),'YYYY-MM-DD HH24') || ':
> 59:59','YYYY-MM-DD HH24:MI:SS')
>         INTO vMAX_PMM_DATETIME FROM om_db.CDRS;
>
>         -- ********************* PARA REPROCESAMIENTO UNICAMENTE
> **********************************
>         -- ESPECIFICAR FECHA INICIAL Y FECHA FINAL
>         --vPMM_DATETIME := '2009-01-01 00:53:00';
>         --vEND_DATETIME := '2009-01-20 15:53:00';
>         --
> ****************************************************************************************
>         WHILE (vPMM_DATETIME <= vMAX_PMM_DATETIME) LOOP
>             OPEN CL_CDR;
>                 FETCH CL_CDR INTO vCALL_SOURCE_REGID;
>             WHILE (CL_CDR%FOUND) LOOP
>                 vTOTAL_CDRS_CLIENTE := 0;
>                 vMIN_VALID_COFETEL := 0;
>                 vMIN_INVALID_COFETEL := 0;
>                 vMOVILES_VALIDOS := 0;
>                 vMOVILES_INVALIDOS := 0;
>                 vFIJOS_VALIDOS := 0;
>                 vFIJOS_INVALIDOS := 0;
>                 vTOTAL_ISDN_CAUSE := 0;
>                 vCALL_DURATION_INT := 0;
>                 -- Obtener Total de CDRS por cliente
>                 SELECT COUNT(CALL_SOURCE_REGID) INTO
> vTOTAL_CDRS_CLIENTE
>                 FROM om_db.CDRS
>                 WHERE START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
>                 AND CALL_SOURCE_REGID = vCALL_SOURCE_REGID;
>
>                 v_rows_processed := 0;
>                 DELETE om_db.CDRS
>                 WHERE START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
>                 AND CALLED_PARTY_ON_DEST LIKE '%*%'
>                 AND CALL_SOURCE_REGID = vCALL_SOURCE_REGID;
>                 v_rows_processed := sql%rowcount;
>

[]

I did not go thru all your code, but this seems to do a LOT of the work procedurally. IWO you do it in the PL/SQL code rather than in SQL queries. If you can rewrite some of this to do more in the queries, it will be faster. It goes slow because you keep going back to do count (*) and select DISTINCT queries.

Again I only spent about 5minutes reviewing this. If this was a package under my control, I would likely rewrite the entire thing. It appears (I hope I cause no offense here) to be written by a procedural programmer who was unfamiliar with the set programming of SQL. The more that can be done by the database engine via SQL the faster the entire package will be.

HTH (Hope That Helps)

   Ed

>
> Thanks in advanced
Received on Thu Feb 19 2009 - 14:22:06 CST

Original text of this message