Re: ACCELERATING STORE PROCEDURE

From: Jorge Reyes <jorg_reyes_at_hotmail.com>
Date: Fri, 20 Feb 2009 10:24:31 -0800 (PST)
Message-ID: <48f32f21-a5c0-486f-a6a9-b8e7b6ae7441_at_e22g2000vbe.googlegroups.com>



On 20 feb, 07:07, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> On Thu, 19 Feb 2009 11:29:14 -0800, Jorge Reyes wrote:
> > Hi everyone,
>
> Hi. Please do not use all capitals in your title. It's an equivalent to
> yelling and is considered to be extremely rude.
>
>
>
> > 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, you're copying from source to destination. It's not that unusual.
>
>
>
> > 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)
>
> That is not enough. The best thing to do would be to execute the procedure
> with the event 10046 turned on to level 12 and then analyze the output
> with Orasrp.
>
>
>
>
>
> > 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;
>
> Why do you have "select distinct"? The "distinct" clause will always
> cause sorting. CDR=="Call Detail Record", this is some kind of
> telecommunication service? If so, there are no two CDR's that are exactly
> the same, there are no duplicates. PABX or switch will simply not produce
> any duplicates. No "distinct" clause is necessary. If it is, you have
> problems with processing your data, even before it comes to your
> procedure.
>
> >     CURSOR L_ISDN IS SELECT NUMERIC_REASON_CODE FROM
> > om_db.CDRS_LISTED_ISDN_CAUSE_CODES ORDER BY NUMERIC_REASON_CODE;
>
> Why would you have "order by" in a cursor declaration?
>
>
>
> > 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');
>
> This is one of the 7 deadly sins: thou shalt not use "select count(*)"
> to establish existence or the lack of it.
>
> >     ELSE
> >         SELECT TO_DATE(TO_CHAR(MIN(START_TIME),'YYYY-MM-DD HH24') || ':
> > 00:00','YYYY-MM-DD HH24:MI:SS')
>
> Why are you converting DATE column to CHAR and then back again to the DATE
> value?
>
> >         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;
>
> >                 vMIN_INVALID_COFETEL := v_rows_processed;
>
> >                 -- Obtener MIN_VALID_COFETEL
> >                 SELECT COUNT(*) INTO vMIN_VALID_COFETEL FROM ( SELECT
> >                 A.*, B.*
> >                 FROM om_db.CDRS A, om_db.cofetel B
> >                 WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> > vEND_DATETIME
> >                 AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> > NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> >                 AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID);
>
> This is going to be a Cartesian join. Are you sure that you want that?
>
>
>
>
>
> >                 -- Calcular el MIN_INVALID_COFETEL
> >                 vMIN_INVALID_COFETEL := vTOTAL_CDRS_CLIENTE -
> > vMIN_VALID_COFETEL;
>
> >                 -- Obtener los MOVILES_VALIDOS
> >                 SELECT COUNT(*) INTO vMOVILES_VALIDOS FROM ( SELECT A.*,
> >                 B.*
> >                 FROM om_db.CDRS A, om_db.cofetel B
> >                 WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> > vEND_DATETIME
> >                 AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> > NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> >                 AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,6) = '140521' AND
> >                 A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID AND B.MODALIDAD
> >                 = 'CPP');
>
> CDR should be split and processed before it's inserted into the database.
> RDBMS is not a good tool for text processing. You should split
> CALLED_PARTY_ON_DEST using some general purpose language like Perl, C or
> Java. Expressions like
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS NUMBER(*,0))
>     between B.NIR_INICIAL AND B.NIR_FINAL
>
> are extremely hard to process. You should add a separate numeric
> column and insert the following value into it by writing a trigger.
>
>  CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS NUMBER(*,0))
>
> You could also use some Perl to extract it from the CDR during the
> initial processing of the PABX files. That would be a less costly option,
> in terms of machine resources.
>
> Do you know Juan Pacheco Reyes?
>
> --http://mgogala.freehostia.com

Greetings from Mexico friendly, Charles and Mladen, about Juan Pacheco Reyes i dont know him, humm.. oh yes my apologies about the capitals it was a mistake, i know are not educated, sorry, i pretend make a combination of both advices from you, unified the 5 querys into 1 and of course release the RDBMS for text processing, so:

  1. Unified querys: Not sure what means, "do the same without PL/SQL" i mean, can i replicate the exactly algorithm from Charles? of course including the real names and rest of the conditions into the sp, or not?
  2. Each of everyone of your notes are very true Mladen, i can not avoid my novice on this right jeje? by the way the "Select Distinct" on

CURSOR CL_CDR IS SELECT DISTINCT(CALL_SOURCE_REGID) FROM om_db.CDRS WHERE CALL_SOURCE_REGID IS NOT NULL;

It is necessary because, its true there are not duplicate CDR's but the CALL_SOURCE_REGID means the phone number of the client, so i have to make all the statistics for each client. Now i have a question, the original raw data is inserted with bcp utility, the best option to release the RDBMS for text processing i think is split CALLED_PARTY_ON_DEST adding a separate numeric column and insert the following value into it by writing a trigger, but is it possible make this considering the bcp utility, in Sybase bcp disables triggers and constraints, in Oracle?

Muchas Gracias, Thanks a lot... Received on Fri Feb 20 2009 - 12:24:31 CST

Original text of this message