here are the ralted stored procedures. I call authorise_str.
sescreate sp inserts the record.

If you want I can dump somewhere my whole db (3.3 MB) and test
scripts.

JS



CREATE OR ALTER PROCEDURE authorise_str(
v_nasid varchar(25),
v_macaddr varchar(25),
v_ipaddr varchar(25),
v_actid varchar(25),
v_password varchar(25), v_actno int, v_appmode varchar(20),
v_vlan_tag varchar(25), v_dest_ip varchar(500), v_svctype int
)
RETURNS (status smallint, authstr varchar(1000))
as
declare v_dest_port int;
declare actno int;
declare session_id varchar(25);
declare actid varchar(25);
declare actstatus smallint;
declare statusdescr varchar(100);
declare idletimeout int;
declare sessiontimeout numeric(18,0);
declare timeouttype smallint;
declare qosno int;
declare v_nasno int;
declare v_domno int;
declare bytesin numeric(18,0);
declare bytesout numeric(18,0);
declare seconds numeric(18,0);
declare bytepulse double precision;
declare timepulse double precision;
declare usesum smallint;
declare subsno int;
declare qosgrpno int;
declare svctype smallint;
declare sescount int;
declare gateway varchar(25);
declare v_internalid varchar(50);
declare v_retval int;
begin
select domno,nasno from ssgnas where nasid=:v_nasid
into :v_domno,:v_nasno;

select
actno,session_id,actstatus,statusdescr,idletimeout,sessiontimeout,time
outtype,

qosno,bytesin,bytesout,seconds,bytepulse,timepulse,usesum,subs
no,
qosgrpno,svctype,sescount,gateway,actid,internalid
from authorise
(:v_domno,:v_macaddr,:v_ipaddr,:v_actid,:v_password,:v_actno,:v_appmod
e,
:v_vlan_tag,:v_dest_ip,:v_svctype)

into :actno,:session_id,:actstatus,:statusdescr,:idletimeout,:
sessiontimeout,:timeouttype,

:qosno,:bytesin,:bytesout,:seconds,:bytepulse,:timepulse,:uses
um,:subsno,

:qosgrpno,:svctype,:sescount,:gateway,:actid,:v_internalid;

authstr='';
status=0;
authstr=authstr||'Acct-Status-Type='||cast(actstatus as varchar(5))
||'|';
authstr=authstr||'Reply-Message='||statusdescr||'|';
if (actstatus=0 or actstatus is null) then begin
SUSPEND;
EXIT;
end
status=1;
authstr=authstr||'Acct-Session-Id='||session_id||'|';
authstr=authstr||'User-UID='||cast(actno as varchar(20))||'|';
authstr=authstr||'User-Name='||actid||'|';
authstr=authstr||'Package-No='||cast(qosgrpno as varchar(20))||'|';
authstr=authstr||'Use-Bytes-Sum='||cast(usesum as varchar(20))||'|';
authstr=authstr||'Idle-Timeout='||cast(idletimeout as varchar(20))
||'|';
authstr=authstr||'Session-Timeout='||cast(sessiontimeout as varchar
(20))||'|';
authstr=authstr||'Session-Timeout-Type='||cast(timeouttype as varchar
(20))||'|';
authstr=authstr||'QoS-Policy-Id='||cast(qosno as varchar(20))||'|';
authstr=authstr||'Max-Bytes-In='||cast(bytesin as varchar(30))||'|';
authstr=authstr||'Max-Bytes-Out='||cast(bytesout as varchar(30))||'|';
authstr=authstr||'Max-Time='||cast(seconds as varchar(30))||'|';
authstr=authstr||'Bytes-Pulse='||cast(bytepulse as varchar(10))||'|';
authstr=authstr||'Time-Pulse='||cast(timepulse as varchar(10))||'|';
if (sescount > 0) then
authstr=authstr||'TCP-Session-Count='||cast(sescount as
varchar(10))||'|';
authstr=authstr||'Service-Type-Id='||cast(svctype as varchar(10))
||'|';
authstr=authstr||'Gateway='||gateway||'|';
/*
insert into session
(sesno,actno,subsno,start,lastupd,bytesin,bytesout,state,ipaddr,macadd
r,lastbytesin,
lastbytesout,internalid,nasno,bytepulse,timepulse,cpno,devid,svctype)
values
(:session_id,:actno,:subsno,current_timestamp ,current_timestamp,0,0,'
W',:v_ipaddr,:v_macaddr,0,0,
:v_internalid,:v_nasno,:bytepulse,:timepulse,0,'',:svctype);
*/


SELECT retval FROM sescreate(:session_id,

:v_ipaddr,:v_macaddr,:actno,:subsno,:v_internalid,

:v_nasno,:bytepulse,:timepulse,'',:svctype)

into :v_retval;


SUSPEND;

end !!

CREATE OR ALTER PROCEDURE authorise(
v_domno int,
v_macaddr varchar(25),
v_ipaddr varchar(25),
v_actid varchar(25),
v_password varchar(25), v_actno int, v_appmode varchar(20),
v_vlan_tag varchar(25), v_dest_ip varchar(500), v_svctype int
)
RETURNS (
actno int,
session_id varchar(25),
actstatus smallint,
statusdescr varchar(100),
idletimeout int,
sessiontimeout numeric(18,0),
timeouttype smallint,
qosno int,
bytesin numeric(18,0),
bytesout numeric(18,0),
seconds numeric(18,0),
bytepulse double precision,
timepulse double precision,
usesum smallint,
subsno int,
qosgrpno int,
svctype smallint,
sescount int,
gateway varchar(25),
actid varchar(25),
internalid varchar(50)
)
AS
declare v_internalid varchar(100);

declare v_status int;
declare v_devid varchar(25);
declare v_actsubsno int;
declare v_grpsubsno int;
declare v_quotasubsno int;
declare v_qossubsno int;
declare v_acttype smallint;
declare v_quotatype smallint;
declare v_grpno int;
declare v_statuscode smallint;
declare v_retval int;
declare v_stno int;
declare u_macaddr varchar(25);
declare u_ipaddr varchar(25);
declare u_actid varchar(25);
declare u_vlan_tag varchar(25);
declare u_explicitlogin smallint;
declare u_bind_ip smallint;
declare u_bind_mac smallint;
declare u_bind_vlan smallint;
declare v_svcno int;
begin

select
actid,gateway,ipaddr,macaddr,vlan_tag,explicitlogin,bind_ip,bind_mac,b
ind_vlan
from account
where actno=:v_actno
into :v_actid,:gateway,
u_ipaddr,:u_macaddr,:u_vlan_tag,:u_explicitlogin,:u_bind_ip,:u_bind_ma
c,:u_bind_vlan;

if (gateway is null) then gateway='';
actstatus=0;
sescount=0;
statusdescr='';
svctype=v_svctype;
select quotasubsno,qosgrpno from getactsvcsubsno(:v_actno,:v_svctype)
into :v_quotasubsno, :v_qossubsno;

if (v_quotasubsno is null or v_quotasubsno=0) then begin
if (v_appmode='control') then begin
statusdescr='Act Id: '||v_actid||' No active
subscription for svctype '||cast(v_svctype as varchar(5));
EXECUTE PROCEDURE writeeventlog
v_domno,statusdescr,cast(v_actno as varchar(20));
SUSPEND;
EXIT;
end
end

select actstatus, statuscode,statusdescr,idletimeout, sessiontimeout,
timeouttype,qosno,
bytesin, bytesout,seconds,bytepulse,timepulse,usesum
from getactbalance(:v_actno,:v_quotasubsno) into
:actstatus, :v_statuscode,:statusdescr,:idletimeout, :sessiont
imeout, :timeouttype,:qosno,:bytesin, :bytesout,:seconds,:bytepulse,:t
imepulse,:usesum;

actno=v_actno;
subsno=v_quotasubsno;
qosgrpno=v_qossubsno;
actid=v_actid;
if (actstatus != 1) then begin

--insert into error_log (actno,descr,errordate,data)
values (:v_actno, :statusdescr,current_timestamp,:v_statuscode);
if (v_appmode='control') then begin
statusdescr='Act Id: '||v_actid||' '||statusdescr;
EXECUTE PROCEDURE writeeventlog
v_domno,statusdescr,cast(v_actno as varchar(20));
SUSPEND;
EXIT;
end
--assign the default policy for un-registered
actstatus=1;
statusdescr=statusdescr||'.Un-Registered authentication.
Mode='||v_appmode;
idletimeout=600;
sessiontimeout=86400; --One day
timeouttype=0;
qosno=0;
bytesin=-1;
bytesout=-1;
seconds=-1;
bytepulse=1;
timepulse=1;
usesum=1;
subsno=0;
qosgrpno=0;
v_quotasubsno=0;
end

session_id=cast(GEN_ID(gen_sesno,1) as varchar(20));
internalid='';
if (u_explicitlogin=1) then begin
if (strlen(internalid)>0) then internalid=internalid||', ';
internalid=internalid||v_actid;
end
if (u_bind_ip=1) then begin
if (strlen(internalid)>0) then internalid=internalid||', ';
internalid=internalid||u_ipaddr;
end
if (u_bind_mac=1) then begin
if (strlen(internalid)>0) then internalid=internalid||', ';
internalid=internalid||u_macaddr;
end
if (u_bind_vlan=1) then begin
if (strlen(internalid)>0) then internalid=internalid||', ';
internalid=internalid||u_vlan_tag;
end
--find the sescount
if (sescount=0 or sescount is null) then begin
select sescount,svcno from subs where subsno=:v_quotasubsno
into :sescount,:v_svcno;
if (sescount=0 or sescount is null) then
select sescount from packages where pkgno=:v_svcno
into :sescount;
end
if (sescount is null) then
sescount=0;

--now start the session
SUSPEND;
end!!


create or alter procedure writeeventlog(v_domno int, v_action varchar
(500), v_data varchar(255))
as
declare v_eventno bigint;
begin

v_eventno=GEN_ID(gen_eventno,1);
/*
insert into event_log(domno,eventno,eventtime,descr,data)
values
(:v_domno,:v_eventno,current_timestamp,:v_action,:v_data);
*/


end !!

CREATE OR ALTER PROCEDURE sescreate (
v_sesno varchar(25),
v_ipaddr varchar(25),
v_macaddr varchar(25),
v_actno int,
v_subsno int,
v_internalid varchar(100),
v_nasno int,
v_bytepulse double precision,
v_timepulse double precision,
v_devid varchar(25),
v_svctype int) RETURNS (retval int)
AS
declare v_cpno numeric(18,0);
declare v_retval int;
declare ts timestamp;
begin
ts=current_timestamp;
--************NEED TO BE VERIFIED
--v_retval=logoutdevice(v_subsno,v_devid,17);
--if (v_actno > 0) then
--update session set state='C',closereason=9 where state='A'
and actno=:v_actno and subsno=:v_subsno;
--else
--update session set state='C',closereason=9 where state='A'
and ipaddr=:v_ipaddr;

--*************
--find out the related coupon no.
select cpno from cpnsubs where actno=:v_actno and subsno=:v_subsno
into :v_cpno ;

insert into session
(sesno,actno,subsno,start,lastupd,bytesin,bytesout,state,ipaddr,macadd
r,lastbytesin,
lastbytesout,internalid,nasno,bytepulse,timepulse,cpno,devid,svctype)
values
(:v_sesno,:v_actno,:v_subsno,:ts ,:ts,0,0,'W',:v_ipaddr,:v_macaddr,0,0
,:v_internalid,:v_nasno,:v_bytepulse,:v_timepulse,:v_cpno,:v_devid,:v_
svctype);
retval=1;
SUSPEND;
end !!





--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
wrote:

> > I think I have narrowed down to the root cause of problem. After
> > doing testing for whole of sunday and today, I found that stored
> > procedures are creating problem. I ran a perl scripts, which
> > continously calls two SPs. One of those SP does an insert into a
> > table.
> > When I do an insert from within the stored procedure, it reached
> > 135MB in 20 minutes. But when I remove that insert from SP, and
> > execute the same insert from the perl script, it reached 43MB in

20

> > minutes.
> >
> > My application uses stored procedure for all queries and updates.

So

> > before I jump into modifying my app where it will not do any
> > insert/updates in SPs, I will appriciate if some expert can give

me

> > some more ideas.
> >
> > Gurus please hep me !!!!
> >
> > JS
>
> is the insert in the SP locked in some loop?
> let's see the SP code?
> Alan