Re: ACCELERATING STORE PROCEDURE

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 19 Feb 2009 13:31:36 -0800 (PST)
Message-ID: <18b5ce54-20e7-4b84-9a1b-a0a5c9df094b_at_b8g2000pre.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)
(Snip)

I agree with Ed - don't do in PL/SQL what may be done with just SQL. Let's take a look at part of the code (note that there *may* be problems with my PL/SQL code):
This part of the code is doing much more work than necessary:

  • 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);
  • 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');
  • Obtener los MOVILES_INVALIDOS SELECT COUNT(*) INTO vMOVILES_INVALIDOS 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');
  • Obtener los FIJOS_VALIDOS SELECT COUNT(*) INTO vFIJOS_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,5) = '14052' AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID AND B.MODALIDAD IN('FIJO','MPP'));
  • Obtener los FIJOS_INVALIDOS SELECT COUNT(*) INTO vFIJOS_INVALIDOS 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,5) <> '14052' AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID AND B.MODALIDAD IN('FIJO','MPP'));
    The above may be done in a single SQL statement, something like this (note: the first character position in a string is 1, not 0 as it is in C++): SELECT SUM(1) INTO vMIN_VALID_COFETEL, SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST, 0,6),'140521',1,0),0)) INTO vMOVILES_VALIDOS, SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST, 0,6),'140521',0,1),0)) INTO vMOVILES_INVALIDOS, SUM(DECODE(B.MODALIDAD,'FIJO',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST, 0,5),'14052',1,0),0))+SUM(DECODE(B.MODALIDAD,'MPP',DECODE(SUBSTR (A.CALLED_PARTY_ON_DEST,0,5),'14052',1,0),0)) INTO vFIJOS_VALIDOS, SUM(DECODE(B.MODALIDAD,'FIJO',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST, 0,5),'14052',0,1),0))+SUM(DECODE(B.MODALIDAD,'MPP',DECODE(SUBSTR (A.CALLED_PARTY_ON_DEST,0,5),'14052',0,1),0)) INTO vFIJOS_INVALIDOS 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);
    • Calcular el MIN_INVALID_COFETEL vMIN_INVALID_COFETEL := vTOTAL_CDRS_CLIENTE - vMIN_VALID_COFETEL;

In the above, SUM and DECODE are used to essentially eliminate 4 additional passes through the tables.

You have an index declared like this:
CREATE INDEX PSA.CDRS_IX ON OM_DB.CDRS(
 START_TIME,
 CALLED_PARTY_ON_DEST,
 CALL_SOURCE_REGID) Due to the CAST statement used on the A.CALLED_PARTY_ON_DEST column, the above index probably would not be used by the query. A function based index might be able to help, for example: CREATE INDEX PSA.CDRS_IX ON OM_DB.CDRS(
 START_TIME,
 CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS NUMBER(*,0)),  CALL_SOURCE_REGID); Since the above set of 5 SQL statements have been decreased to a single SQL statement, you might then experiment with using the SQL statement to directly update the om_db.CDRS_FINAL1 table with the single SQL statement. I will leave that for someone else to explain.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Feb 19 2009 - 15:31:36 CST

Original text of this message