Home » SQL & PL/SQL » SQL & PL/SQL » optimize this procedure..
optimize this procedure.. [message #240512] Fri, 25 May 2007 02:05 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
I have a table called T_CLIENT_PC. The structure is:

-- Create table
drop table T_CLIENT_PC;
create table T_CLIENT_PC
(
  CLIENT_ID            NUMBER not null,
  CATEG_PROD_ID        NUMBER not null,
  CLIENT_PROD_ID       VARCHAR2(32) not null,
  CLIENT_PROD_DESC     VARCHAR2(128) not null,
  CLIENT_CATEG_ID      VARCHAR2(32) not null,
  CLIENT_CATEG_DESC    VARCHAR2(128) not null,
  TS                   DATE
);


It has indexes on client_id and categ_prod_id.

What my procedure below does is to make sure that all records belonging to particular client_id and categ_prod_id should have the same client property values (client_prod_id, client_prod_desc, client_categ_id, client_categ_desc). It does so by getting one of them which got changed recently (yesterday, through a cursor) and update the rest. This procedure is running very slow. I don't know if there's a better way to change the logic to make it faster:

create or replace procedure my_test is

    v_client_pc_max_ts date;

    v_client_prod_id       t_client_pc.client_prod_id%type;
    v_client_prod_desc     t_client_pc.client_prod_desc%type;
    v_client_categ_id      t_client_pc.client_categ_id%type;
    v_client_categ_desc    t_client_pc.client_categ_desc%type;

   cursor cpc_cursor is
      select max(ts) max_ts, t.client_id, t.categ_prod_id
      from t_client_pc t
      where t.ts >= v_client_pc_max_ts
      group by t.client_id, t.categ_prod_id;

begin

    v_client_pc_max_ts := sysdate - 1;

    for cpc_rec in cpc_cursor
    loop
         select t.client_prod_id,
                t.client_prod_desc,
                t.client_categ_id,
                t.client_categ_desc
           into v_client_prod_id,
                v_client_prod_desc,
                v_client_categ_id,
                v_client_categ_desc
           from t_client_pc t
          where t.client_id     = cpc_rec.client_id
            and t.categ_prod_id = cpc_rec.categ_prod_id
            and t.ts            = cpc_rec.max_ts
            and rownum          = 1;

        -- update the rest of records having same client_id and categ_prod_id  
        update t_client_pc t
           set t.client_prod_id      = v_client_prod_id ,
               t.client_prod_desc    = v_client_prod_desc ,
               t.client_categ_id     = v_client_categ_id ,
               t.client_categ_desc   = v_client_categ_desc
         where t.client_id           = cpc_rec.client_id
           and t.categ_prod_id       = cpc_rec.categ_prod_id
           and ( t.client_prod_id       <> v_client_prod_id
                 or t.client_prod_desc  <> v_client_prod_desc
                 or t.client_categ_id   <> v_client_categ_id
                 or t.client_categ_desc <> v_client_categ_desc
               );
    end loop;
    commit;
end;
Re: optimize this procedure.. [message #240518 is a reply to message #240512] Fri, 25 May 2007 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems to me you can do it in a single update statement without any PL/SQL.

Regards
Michel
Re: optimize this procedure.. [message #240596 is a reply to message #240518] Fri, 25 May 2007 05:10 Go to previous messageGo to next message
beetel
Messages: 96
Registered: April 2007
Member
But that would be more complex and slow, right?
Re: optimize this procedure.. [message #240597 is a reply to message #240596] Fri, 25 May 2007 05:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No, that would be simpler and faster
Re: optimize this procedure.. [message #240605 is a reply to message #240596] Fri, 25 May 2007 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Far more faster.

Regards
Michel
Re: optimize this procedure.. [message #240629 is a reply to message #240512] Fri, 25 May 2007 06:30 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The problem with the existing procedure is that you are executing the inline select and update for every row of the cpc_cursor. Even if these statements are optimized as well as possible (using indexes to find the rows), executing them repeatedly like that will give very poor performance.

For example, every single time the select is called, there is a context switch from PL/SQL to SQL and Oracle has to open, fetch and close a cursor. Procedures which involve a cursor being opened and closed millions of times are almost invariably slow. Also, from the fact that you are having to add a rownum condition to prevent an ORA-01422, it plainly isn't a unique key lookup, so may not be very well optimized.
Previous Topic: PLS-00371: at most one declaration for 'V_MSG' is permitted
Next Topic: Problem with odbc
Goto Forum:
  


Current Time: Sat Dec 03 20:07:32 CST 2016

Total time taken to generate the page: 0.10842 seconds