Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Performance (Oracle 10g, XP)
Procedure Performance [message #563863] Fri, 17 August 2012 10:49 Go to next message
manirocks
Messages: 65
Registered: October 2007
Member
Oracle 10g, Windows XP

There is an interface table and there is an normal transcational table..interface table is being
compared with normal table and if match found the result is dumped into another normal table.

I am using two cursors one is to query the interface table and in a for loop pass the results to the
second cursor..The interface table is having 5000 + rows and the transcation table is having more than
3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes......

create table x_interface /* INterface table */ -- 5000 + rows 
( name       varchar2(80),  addr_line1 varchar2(35),  addr_line2 varchar2(35),  addr_line3 varchar2(35),
  addr_line4 varchar2(35),  addr_line5 varchar2(35),  addr_line6 varchar2(35),  suffix     varchar2(35),
  city       varchar2(15),  state      varchar2(10),  zcode      varchar2(10))
/ 
 
create table x_transtable /*Transcational table*/ -- 3.7 Million rows
( id    	 number,  	 	name          varchar2(80),  addr_line1 varchar2(35),  addr_line2 varchar2(35),
  addr_line3 varchar2(35),  addr_line4 	  varchar2(35),  addr_line5 varchar2(35),  addr_line6 varchar2(35),
  suffix     varchar2(35),  city       	  varchar2(15),  state      varchar2(10),  zcode      varchar2(10),
  created_by varchar2(10),  creation_date date
)
/ 
 
create table x_normaltable  /*normal table*/
( rep_date date, id number, remarks varchar2(80))



Program

CREATE OR REPLACE PROCEDURE T14 is
n_start number;
n_end number;
 
CURSOR C1 IS
SELECT
NAME,
DECODE(NVL(SUFFIX,0),'0',ADDR_LINE1,ADDR_LINE1||' '||SUFFIX)ADDR_LINE1,
DECODE(NVL(SUFFIX,0),'0',ADDR_LINE2,ADDR_LINE2||' '||SUFFIX)ADDR_LINE2,
DECODE(NVL(SUFFIX,0),'0',ADDR_LINE3,ADDR_LINE3||' '||SUFFIX)ADDR_LINE3,
DECODE(NVL(SUFFIX,0),'0',ADDR_LINE4,ADDR_LINE4||' '||SUFFIX)ADDR_LINE4,
DECODE(NVL(SUFFIX,0),'0',ADDR_LINE5,ADDR_LINE5||' '||SUFFIX)ADDR_LINE5,
DECODE(NVL(SUFFIX,0),'0',ADDR_LINE6,ADDR_LINE6||' '||SUFFIX)ADDR_LINE6,
SUFFIX,
CITY,
STATE,
ZCODE
FROM x_interface;
 
CURSOR c2(P_ADDR VARCHAR2, P_STATE VARCHAR2, P_ZCODE VARCHAR2, P_CITY VARCHAR2) IS
SELECT ID FROM x_transtable
WHERE UPPER(LTRIM(RTRIM(STATE))) = P_STATE
AND   LTRIM(RTRIM(ZCODE))	 = P_ZCODE
AND   UPPER(LTRIM(RTRIM(CITY))) LIKE '%'||P_CITY||'%'
AND
(
UPPER(LTRIM(RTRIM(ADDR_LINE1))) LIKE '%'||P_ADDR||'%'
or
UPPER(LTRIM(RTRIM(ADDR_LINE2))) LIKE '%'||P_ADDR||'%'
or
UPPER(LTRIM(RTRIM(ADDR_LINE3))) LIKE '%'||P_ADDR||'%'
or
UPPER(LTRIM(RTRIM(ADDR_LINE4))) LIKE '%'||P_ADDR||'%'
or
UPPER(LTRIM(RTRIM(ADDR_LINE5))) LIKE '%'||P_ADDR||'%'
or
UPPER(LTRIM(RTRIM(ADDR_LINE6))) LIKE '%'||P_ADDR||'%'
);
 
BEGIN
 
n_start := dbms_utility.get_time();
 
 FOR I IN C1 LOOP
   IF I.ADDR_LINE1 IS NOT NULL THEN
 
     	FOR N IN C2(I.ADDR_LINE1,I.STATE,I.ZCODE,I.CITY) LOOP
 
 
		INSERT INTO x_normaltable(REP_DATE,id,REMARKS)VALUES
		(SYSDATE, N.ID, 'OK');
 
        END LOOP;
 
   END IF;
 
   EXIT WHEN C1%NOTFOUND;
 
   END LOOP;
 
   COMMIT;
 
n_end := dbms_utility.get_time();
 
dbms_output.put_line('time->'||((n_end - n_start) / 100 ) / 60 );
 
END;
/ 

Re: Procedure Performance [message #563867 is a reply to message #563863] Fri, 17 August 2012 10:57 Go to previous messageGo to next message
BlackSwan
Messages: 22490
Registered: January 2009
Senior Member
row by row is slow by slow
NEVER do in PL/SQL what can be done in plain SQL

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) EXPLAIN PLAN
2) output from SQL_TRACE & tkprof
Re: Procedure Performance [message #563871 is a reply to message #563867] Fri, 17 August 2012 11:33 Go to previous messageGo to next message
manirocks
Messages: 65
Registered: October 2007
Member
I dont think I've neccessary privileges to provide you more details..

can the cursor c2 can be modified to a single sql statement like INSERT into table (<select>) .. or to bulk collect/for all concept.
Re: Procedure Performance [message #563872 is a reply to message #563871] Fri, 17 August 2012 11:35 Go to previous messageGo to next message
BlackSwan
Messages: 22490
Registered: January 2009
Senior Member
>can the cursor c2 can be modified to a single sql statement like INSERT into table (<select>)
YES & single INSERT will perform better than posted PL/SQL procedure.
Re: Procedure Performance [message #563874 is a reply to message #563872] Fri, 17 August 2012 11:39 Go to previous messageGo to next message
manirocks
Messages: 65
Registered: October 2007
Member
Okay..I've tried writing this..but getting difference of results..can you look into this..

    insert into x_normaltable
      ( rep_date
      , id
      , remarks
      )
    select sysdate
    ,      t.id
    ,      'ok'
    from   x_interface  i
      join x_transtable t on 
               (   t.state = i.state 
               and t.zcode = i.zcode 
               and t.city like '%'||i.city||'%'
               and (  t.addr_line1 like '%'||i.addr_line1|| (case i.suffix when null then null else ' '||i.suffix end)||'%'
                   or t.addr_line2 like '%'||i.addr_line1|| (case i.suffix when null then null else ' '||i.suffix end)||'%'
                   or t.addr_line3 like '%'||i.addr_line1|| (case i.suffix when null then null else ' '||i.suffix end)||'%'
                   or t.addr_line4 like '%'||i.addr_line1|| (case i.suffix when null then null else ' '||i.suffix end)||'%'
                   or t.addr_line5 like '%'||i.addr_line1|| (case i.suffix when null then null else ' '||i.suffix end)||'%'
                   or t.addr_line6 like '%'||i.addr_line1|| (case i.suffix when null then null else ' '||i.suffix end)||'%'
                   )
               )
    where  i.addr_line1 is not null;

Re: Procedure Performance [message #563876 is a reply to message #563874] Fri, 17 August 2012 11:53 Go to previous messageGo to next message
BlackSwan
Messages: 22490
Registered: January 2009
Senior Member
since I don't have any data I can't test below for differences
INSERT INTO x_normaltable 
            (rep_date, 
             id, 
             remarks) 
SELECT SYSDATE, 
       t.id, 
       'ok' 
FROM   x_transtable t 
       join x_interface i 
         ON ( t.state = i.state 
              AND t.zcode = i.zcode 
              AND t.city LIKE '%' 
                              ||i.city 
                              ||'%' 
              AND ( t.addr_line1 LIKE '%' 
                                      ||i.addr_line1 
                                      || ( CASE i.suffix 
                                             WHEN NULL THEN NULL 
                                             ELSE ' ' 
                                                  ||i.suffix 
                                           END ) 
                                      ||'%' 
                     OR t.addr_line2 LIKE '%' 
                                          ||i.addr_line1 
                                          || ( CASE i.suffix 
                                                 WHEN NULL THEN NULL 
                                                 ELSE ' ' 
                                                      ||i.suffix 
                                               END ) 
                                          ||'%' 
                     OR t.addr_line3 LIKE '%' 
                                          ||i.addr_line1 
                                          || ( CASE i.suffix 
                                                 WHEN NULL THEN NULL 
                                                 ELSE ' ' 
                                                      ||i.suffix 
                                               END ) 
                                          ||'%' 
                     OR t.addr_line4 LIKE '%' 
                                          ||i.addr_line1 
                                          || ( CASE i.suffix 
                                                 WHEN NULL THEN NULL 
                                                 ELSE ' ' 
                                                      ||i.suffix 
                                               END ) 
                                          ||'%' 
                     OR t.addr_line5 LIKE '%' 
                                          ||i.addr_line1 
                                          || ( CASE i.suffix 
                                                 WHEN NULL THEN NULL 
                                                 ELSE ' ' 
                                                      ||i.suffix 
                                               END ) 
                                          ||'%' 
                     OR t.addr_line6 LIKE '%' 
                                          ||i.addr_line1 
                                          || ( CASE i.suffix 
                                                 WHEN NULL THEN NULL 
                                                 ELSE ' ' 
                                                      ||i.suffix 
                                               END ) 
                                          ||'%' ) ) 
WHERE  i.addr_line1 IS NOT NULL; 
Re: Procedure Performance [message #563903 is a reply to message #563876] Fri, 17 August 2012 14:23 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
DECODE(NVL(SUFFIX,0),'0',ADDR_LINE1,ADDR_LINE1||' '||SUFFIX)ADDR_LINE1,

What happens if the suffix is 0?
Re: Procedure Performance [message #563904 is a reply to message #563903] Fri, 17 August 2012 14:25 Go to previous message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
And I'd recommend a with clause or inline view so you have to do the case on addr_line1 once
Previous Topic: split result set into column (merged 6)
Next Topic: ORA-00947: not enough values
Goto Forum:
  


Current Time: Thu Jul 24 15:48:18 CDT 2014

Total time taken to generate the page: 0.06511 seconds