Re: Performance problems on Informix 7.x on HP UX 10.0x on HP9000

From: "Frank T. Ramb'l" <news@d-fect.com> > >At work we have a large database which includes the tables tracked_ids >and image_ids among others. > >tracked_ids have the following rows: > scanner integer > image_id char(33) > chk_count integer > scandate date > >image_ids consist of: > image_id char(33) > >I run the following script: >=== >begin work; >set lock mode to wait;> >create table ftr_temp_strings (id char(11));> >insert into ftr_temp_strings select image_id[16,26] from image_ids;> >update tracked_ids set chk_count=-1 > where image_id in (select id from ftr_temp_strings);> >delete from image_ids > where image_id[16,26] in (select image_id from tracked_ids where>chk_count=-1); > >delete from tracked_ids where chk_count=-1;> >drop table ftr_temp_strings;> >commit work; >=== > >The problem is that it stops with errormessage "458: Long transaction >aborted." in the "insert into ftr_temp_strings"-line. > >I have also tried using this version: >=== >begin work; >set lock mode to wait;> >update tracked_ids set chk_count=-1 > where image_id in (select image_id[16,26] from image_ids);> >delete from image_ids > where image_id[16,26] in (select image_id from tracked_ids where>chk_count=-1); > >delete from tracked_ids where chk_count=-1;> >commit work; >=== > >The problem here is the same. It times out on the first >update-statement. That is why we made the other version because that is >somewhat faster. > >Any help, ideas, hints whatever that can help me with this problem is >very much apreciated. > >If there is any more information you want or need to answer me, just ask >and I will try to find out. Add more logical logs using the onparams command. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

It seems that you got a long transaction. You need to either increase the logical log size, or turn off the logical log. Good lucks. Ben. Obnoxio The Clown <obnoxio@hotmail.com> wrote in article <89vup3$acp$1@news.xmission.com>... > > From: "Frank T. Ramb'l" <news@d-fect.com> > > > >At work we have a large database which includes the tables tracked_ids > >and image_ids among others. > > > >tracked_ids have the following rows: > > scanner integer > > image_id char(33) > > chk_count integer > > scandate date > > > >image_ids consist of: > > image_id char(33) > > > >I run the following script: > >=== > >begin work; > >set lock mode to wait;> > > >create table ftr_temp_strings (id char(11));> > > >insert into ftr_temp_strings select image_id[16,26] from image_ids;> > > >update tracked_ids set chk_count=-1 > > where image_id in (select id from ftr_temp_strings);> > > >delete from image_ids > > where image_id[16,26] in (select image_id from tracked_ids where> >chk_count=-1); > > > >delete from tracked_ids where chk_count=-1;> > > >drop table ftr_temp_strings;> > > >commit work; > >=== > > > >The problem is that it stops with errormessage "458: Long transaction > >aborted." in the "insert into ftr_temp_strings"-line. > > > >I have also tried using this version: > >=== > >begin work; > >set lock mode to wait;> > > >update tracked_ids set chk_count=-1 > > where image_id in (select image_id[16,26] from image_ids);> > > >delete from image_ids > > where image_id[16,26] in (select image_id from tracked_ids where> >chk_count=-1); > > > >delete from tracked_ids where chk_count=-1;> > > >commit work; > >=== > > > >The problem here is the same. It times out on the first > >update-statement. That is why we made the other version because that is > >somewhat faster. > > > >Any help, ideas, hints whatever that can help me with this problem is > >very much apreciated. > > > >If there is any more information you want or need to answer me, just ask > >and I will try to find out. > > Add more logical logs using the onparams command. > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com > >