I doubt it's the committing that's problem. It's more likely to be server
memory and your attempt to use too much of it in one hit. If instead of
using e.g. select * from table1 where table1 has 300000 records, use several
for select * from table1 where foreignkey=:variable into etc do begin
end
loops where variable is a distinct value already selected

I have rooms inside buildings inside schools inside districts (total 470,000
rooms) when I want to process all rooms I use this structure. It can process
very quickly compared with any attempt to process rooms from a select * from
rooms query

FOR SELECT DISTID FROM DISTRICTS WHERE mycondition="T" INTO :DIST
DO BEGIN
FOR SELECT SRECNO FROM SCHOOLS WHERE DSEDOFF=:DIST INTO :SREC
DO BEGIN
FOR SELECT BNO FROM BUILDINGS WHERE SRECNO=:SREC INTO :BBNO
DO BEGIN
<my code to process rooms>
END
END
END

If you watch with some debugging code you can compare this process with the
select * from structure, the select * from table structure takes all it's
time in the select * from table call before you even get to any processing
code.

Alan
-----Original Message-----
From: Bayu [mailto:bayu@...]
Sent: Tuesday, 28 January 2003 1:36 PM
To: IBObjects
Subject: [IBO] Commit in Stored Procedure


Hi all,

My production system is about processing update about 300.000 records every
night.
And this processing is in stored procedure

If i execute the sp on this records it takes a long time ... and then
commited
But when execute with divide this record and processing about 50.000 records
in each execute sp, this takes a short time.

How can i set the sp which every 5.000 will commit this update with IBO ?

Sorry for my english

regards



___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/