Re: performance tuning in large function / transaction

Hi all,

  table :
  application : 220 rows ;
  usermenu    : 6055 rows ;

M.T.

--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
> On Fri, 14 Dec 2001, MindTerm wrote:
>
> > Hi all,
> >
> >   senario:
> >
> >   cursor 1 loop ( e.g. find student id )
> >    cursor 2 loop ( e.g. find courses of this
> student )
> >     cursor 3 loop ( e.g. update course information
> )
> >       delele course detail ...
> >       or
> >       delete course detail ... ( same primary key
> )
> >       insert course detail ... ( same primary key
> )
> >     end loop 3
> >    end loop 2
> >   end loop 1
> >
> >   It did 75 delete actions 140 update actions (
> delete
> > and insert ). The process time was about 5-6
> minutes
> > while oracle was 10 seconds to 20 seconds .
>
> Can you send the actual function and table schema
> for the associated
> tables and sizes of the tables involved (I figure
> the data itself is
> probably not available, but we may be able to see
> what's going on then)
>
>


__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com-- Table: usermenu
CREATE TABLE "usermenu" (
  "um_user" numeric(11, 0) NOT NULL,
  "um_app_id" numeric(11, 0) NOT NULL,
  "um_order" varchar(50),
  "um_gif" varchar(100),
  "um_alt" varchar(50),
  "um_link" varchar(100),
  "um_level" varchar(5),
  "um_initial" varchar(1),
  "um_gallery_id" numeric(11, 0),
  "um_en_length" numeric(4, 0),
  "um_tc_length" numeric(4, 0),
  "um_sc_length" numeric(4, 0),
  "um_terminator" varchar(1),
  "um_menu" varchar(1),
  "um_en_name" varchar(1000),
  "um_tc_name" varchar(1000),
  "um_sc_name" varchar(1000),
  "um_ext" varchar(1),
  CONSTRAINT "usermenu_pkey" PRIMARY KEY ("um_user", "um_app_id"),
  CONSTRAINT "usermenu_fk1992931308295" FOREIGN KEY (um_app_id) REFERENCES "application" (app_id)
);

-- Table: application
CREATE TABLE "application" (
  "app_id" numeric(11, 0) NOT NULL,
  "app_en_name" varchar(100) NOT NULL,
  "app_tc_name" varchar(100),
  "app_sc_name" varchar(100),
  "app_menu" varchar(1),
  "app_installed" varchar(1),
  "app_order" varchar(50),
  "app_alt" varchar(50),
  "app_gif" varchar(100),
  "app_link" varchar(100),
  "app_initial" varchar(1),
  "app_gallery_id" numeric(11, 0),
  "app_terminator" varchar(1),
  "app_en_length" numeric(4, 0),
  "app_tc_length" numeric(4, 0),
  "app_sc_length" numeric(4, 0),
  "app_ext" varchar(1),
  "app_type" varchar(30),
  CONSTRAINT "application_pkey" PRIMARY KEY ("app_id")
);



BEGIN TRANSACTION;

drop function hku_usermenu( numeric, varchar, int, varchar );
CREATE FUNCTION hku_USERMENU ( numeric, varchar , int , varchar ) RETURNS varchar AS '
DECLARE
 vGroupId        ALIAS FOR $1 ;
 vUserID         ALIAS FOR $2 ;
 vGalleryId      ALIAS FOR $3 ;
 rSuccess        ALIAS FOR $4 ;
 vTmpUserID      user_group.ug_user_id%TYPE;
 vTmpGroupID     user_group.ug_id%TYPE;
 vTmpOrder       application.app_order%TYPE;
 vAclLevel       acl.acl_level%TYPE;
 vAppId          application.app_id%TYPE;
 vAppMenu        application.app_menu%TYPE;
 vAppInstalled   application.app_installed%TYPE;
 vAppTerminator  application.app_terminator%TYPE;
 vAppOrder       application.app_order%TYPE;
 vAppOrderLen    int:=0;
 vLength         int:=0;
 vOrder          application.app_order%TYPE;
 app_rec         application%ROWTYPE;
 cnt1            int:=0;
 cnt2            int:=0;
 vcnt            varchar(10):='''';
 v_str           varchar(1500):='''';
 c1              record ;
 c2              record ;
 tmptime         timestamp;
 t1              int := 0 ;
 t2              int := 0 ;
BEGIN

select timeofday() into tmptime ;
RAISE NOTICE ''[MT][START]HKU_USERMENU % % % %'', vGroupId, vUserID, vGalleryId, tmptime ;

  if vUserID = '' '' or vUserID = '''' or vUserID is null then
    vTmpGroupID := vGroupId;
  else
    vTmpGroupID := 0;
  end if;

  for c1  in
    select user_id from (
    (SELECT UG_USER_ID as user_id
     FROM   USER_GROUP
     WHERE  UG_ID          = vTmpGroupID
     and    GALLERY_ID     = vGalleryId)
    union
    (SELECT USER_ID as user_id
     FROM   HKU_USERS
     WHERE  text( USER_ID )   = vUserId
     and    GALLERY_ID     = vGalleryId)
    ) tmp
  loop
        vTmpUserID := text( c1.user_id );

        delete from usermenu where um_user = vTmpUserID ;
        for c2 in
        select ACL_APP, max(ACL_LEVEL) as acl_level, app.APP_ORDER, length(app.APP_ORDER) as app_order_length,
app.APP_MENU,app.app_installed, app.app_terminator 
        from   ACL, APPLICATION app
        where  ACL_APP            = app.APP_ID
        and    ACL_GALLERY_ID     = vGalleryId
        and    app.APP_GALLERY_ID = vGalleryId
        and    ACL_GRP in
          (select u.ug_id
           from   user_group u, user_group_master ug
           where  u.ug_user_id = vTmpUserID
           and    ug.ug_id     = u.ug_id
           and   (ug.deleted   = ''N'' or ug.deleted IS NULL )
           and    u.gallery_id = vGalleryID
           and    ug.gallery_id = vGalleryID
          )
        group by acl_app, app.app_order,app.APP_MENU, app.app_installed, app.app_terminator
        order  by app.app_order desc, ACL_LEVEL
        loop

          vAppId         := c2.ACL_APP ;
          vAclLevel      := c2.acl_level ;
          vAppOrder      := c2.APP_ORDER ;
          vAppOrderLen   := c2.app_order_length ;
          vAppMenu       := c2.APP_MENU ;
          vAppInstalled  := c2.app_installed ;
          vAppTerminator := c2.app_terminator ;

       vcnt := 0;
       vLength := vAppOrderLen;
       vTmpOrder  := vAppOrder;
       select count(*) into vcnt
       from   ACL, APPLICATION app
       where  ACL_APP            = app.app_id
       and    ACL_LEVEL          > 0
       and    APP_INSTALLED      = ''Y''
       and    ACL_GALLERY_ID     = vGalleryID
       and    app.APP_GALLERY_ID = vGalleryID
       and    substr(app.app_order,1,vAppOrderLen) = vAppOrder
       and    app.app_order      <> vAppOrder
       and    ACL_GRP in
              (select u.ug_id
               from   user_group u, user_group_master ug
               where  u.ug_user_id = vTmpUserID
               and    ug.ug_id     = u.ug_id
               and   (ug.deleted   = ''N'' or ug.deleted IS NULL)
               and    u.gallery_id = vGalleryID
               and    ug.gallery_id = vGalleryID);


       IF vAclLevel IS NULL or vAclLevel <= 0  or upper(vAppInstalled) = ''N'' THEN
         delete from usermenu where um_user = vTmpUserID and substr( um_order, 1, vAppOrderLen ) = vAppOrder;

         t1 = t1 + 1 ;

       ELSE IF vcnt > 0 or vAppTerminator = ''Y'' THEN
         for app_rec in SELECT * FROM APPLICATION WHERE app_order = vTmpOrder and  app_gallery_id = vGalleryId
         loop

           delete from usermenu where um_user = vTmpUserID and um_app_id = app_rec.app_id;
             insert into usermenu(um_user, um_app_id, um_order, um_level, um_menu, um_gallery_id,
                     um_gif, um_alt, um_link, um_terminator,
                     um_en_name, um_tc_name, um_sc_name, um_ext)
                    values (vTmpUserID, app_rec.app_id, app_rec.app_order, text( vAclLevel ) , app_rec.app_menu,
vGalleryId,
                     app_rec.app_gif, app_rec.app_alt, app_rec.app_link, app_rec.app_terminator,
                     app_rec.app_en_name, app_rec.app_tc_name, app_rec.app_sc_name, app_rec.app_ext);

/*RAISE NOTICE ''insert into usermenu(um_user, um_app_id, um_order, um_level, um_menu, um_gallery_id,
                     um_gif, um_alt, um_link, um_terminator,
                     um_en_name, um_tc_name, um_sc_name, um_ext)
                    values ( %, % , ''''%'''', % , ''''%'''', % , ''''%'''', ''''%'''' , ''''%'''', ''''%'''' ,
''''%'''',''''%'''' , ''''%'''', ''''%'''' ); ''                      
                    ,
                    vTmpUserID, app_rec.app_id, app_rec.app_order,  vAclLevel , app_rec.app_menu, vGalleryId,
                     app_rec.app_gif, app_rec.app_alt, app_rec.app_link, app_rec.app_terminator,
                     app_rec.app_en_name, app_rec.app_tc_name, app_rec.app_sc_name, app_rec.app_ext ; */

           t2 = t2 + 1 ;

           /* SELECT count(*) into rSuccess from usermenu  WHERE um_user = vTmpUserID  and   um_app_id =
app_rec.app_id;*/ 

           EXIT; /* run once (if c3%FOUND then) */

         end loop;
       END IF;
       END IF;
       vcnt := 0;
     end loop;

   end loop;

select timeofday() into tmptime ;
RAISE NOTICE ''[MT][STOP]HKU_USERMENU % % % % % %'', vGroupId, vUserID, vGalleryId, tmptime, t1, t2 ;


return ''Y'' ;

END;
' LANGUAGE 'plpgsql' ;