pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed From: MindTerm <mindterm@yahoo.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: performance tuning in large function / transaction
Date: Fri, 14 Dec 2001 01:06:47 -0800 (PST)
Message-ID: <20011214090647.31355.qmail@web20205.mail.yahoo.com> (raw )
In-Reply-To: <20011214003753.X8954-100000@megazone23.bigpanda.com >
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
Attachments:
xxx (text/plain, 7.5K) download
-- 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' ;
view thread (23+ messages) latest in thread
Message-ID: <20011214090647.31355.qmail@web20205.mail.yahoo.com>
Permalink: ../20011214090647.31355.qmail@web20205.mail.yahoo.com/
Also on: postgresql.org/message-id/20011214090647.31355.qmail@web20205.mail.yahoo.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mindterm@yahoo.com, sszabo@megazone23.bigpanda.com
Subject: Re: performance tuning in large function / transaction
In-Reply-To: <20011214090647.31355.qmail@web20205.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox