Re: ACCELERATING STORE PROCEDURE
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:
- 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?
- 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