Re: Help optimizing query (newbie)

Just a quick thought. If you are using transation logging it will be worth will creating the temp table with the no log option so it does not to any transaction logging. Nayan Jain wrote in message <7lapvi$3im$1@news.xmission.com>... > >Hi ! > >Just thought of correcting one things here > > >On Mon, 28 Jun 1999, Peter Sloboda wrote: > >> Frank T. Ramb=F8l wrote: >>=20 >> > Hi. >> > >> > I am trying to run the following statements on a rather huge database. >> > >> > =3D=3D >> > begin work; >> > set lock mode to wait;>> > >> > update tracked_ids set chk_count=3D-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=3D-1); >> > >> > delete from tracked_ids where chk_count=3D-1;>> > >> > commit work; >> > =3D=3D >> > >> > The problem is that it hogs 97% of CPU and after running 12 hours(!) it >> > had only completed the first update statement. >> > At that point it had updated ca. 560000 rows. >> > >> > The tables have the foloving fields: >> > tracked_ids: >> > scanner intr >> > image_id char(33) >> > chk_count int >> > scandate date >> > >> > image_ids >> > image_id char(33) >> > >> > I am not sure about the Informic version, but it is 7.xx running on >> > HP-UX 10.20. >> > >> > If anyone could come up with a solution on how to optimize the search I >> > would be very gratefull! I am very new to sql, dbaccess and stuff so I >> > do not know what I can do to make it faster. >> > >> > Regards >> > Frank T. Ramb=F8l >> > >> > -- >> > I started out with nothing & still have most of it left. >>=20 >> Hi ! >> I guess your problem maybe trimming those strings (image_id[16,26]). >> Try something like: >>=20 >> create temp_strings (id char); > > >CREATE TABLE temp_strings (id char(11));> >otherwise it will create table with char(1) and will ntot server the=20 >purpose. > >Thanks ! > >Nayan Jain=20 > >> insert into temp_string select image_id[16,26] from image_ids update>> ....... where image_id in (select id from temp_string) . . . drop >> table temp_strings >>=20 > > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "One >of the reasons for the fall of the Roman empire was that, lacking zero, >they had no way to indicate normal termination of their C programs." >=09=09=09=09=09- Robert Firth=20 > >

Well depending on your hardware and Informix setup you may want to try some of the following.. If you have the tables fragmented over several dbspaces which are also on seperate physical discs on seperate scsii channels.. try this. Assuming your Informix has PDQ, Parallel Data Query. set indexes for tracked_ids disabled; set indexes for image_ids disabled; set pdqpriority 100; begin work; do statements. commit work; --enable indexes. Doing those updates then deletes.. would have to update the indexes each time.. let PDQ take the strain of processing rather than relying on indexes. Another thing to try is the use of exists rather than in. update tracked_ids set chk_count=3D-1 where exists ( select * from image_ids where image_ids.image_id[16,26] = tracked_ids.image_id ) delete from image_ids where exists ( select * from tracked_ids where tracker_ids.image_id = image_ids.image_id[16,26] and chk_count=3D-1 ) If using PDQ.. use IN though! pankaj <pankaj.patel@punks.demon.co.uk> wrote in message news:932639886.24606.0.nnrp-14.d4e5c55a@news.demon.co.uk... > Just a quick thought. If you are using transation logging it will be worth > will creating the temp table with the no log option so it does not to any > transaction logging. > > > > Nayan Jain wrote in message <7lapvi$3im$1@news.xmission.com>... > > > >Hi ! > > > >Just thought of correcting one things here > > > > > >On Mon, 28 Jun 1999, Peter Sloboda wrote: > > > >> Frank T. Ramb=F8l wrote: > >>=20 > >> > Hi. > >> > > >> > I am trying to run the following statements on a rather huge database. > >> > > >> > =3D=3D > >> > begin work; > >> > set lock mode to wait;> >> > > >> > update tracked_ids set chk_count=3D-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=3D-1); > >> > > >> > delete from tracked_ids where chk_count=3D-1;> >> > > >> > commit work; > >> > =3D=3D > >> > > >> > The problem is that it hogs 97% of CPU and after running 12 hours(!) it > >> > had only completed the first update statement. > >> > At that point it had updated ca. 560000 rows. > >> > > >> > The tables have the foloving fields: > >> > tracked_ids: > >> > scanner intr > >> > image_id char(33) > >> > chk_count int > >> > scandate date > >> > > >> > image_ids > >> > image_id char(33) > >> > > >> > I am not sure about the Informic version, but it is 7.xx running on > >> > HP-UX 10.20. > >> > > >> > If anyone could come up with a solution on how to optimize the search I > >> > would be very gratefull! I am very new to sql, dbaccess and stuff so I > >> > do not know what I can do to make it faster. > >> > > >> > Regards > >> > Frank T. Ramb=F8l > >> > > >> > -- > >> > I started out with nothing & still have most of it left. > >>=20 > >> Hi ! > >> I guess your problem maybe trimming those strings (image_id[16,26]). > >> Try something like: > >>=20 > >> create temp_strings (id char); > > > > > >CREATE TABLE temp_strings (id char(11));> > > >otherwise it will create table with char(1) and will ntot server the=20 > >purpose. > > > >Thanks ! > > > >Nayan Jain=20 > > > >> insert into temp_string select image_id[16,26] from image_ids update> >> ....... where image_id in (select id from temp_string) . . . drop > >> table temp_strings > >>=20 > > > > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "One > >of the reasons for the fall of the Roman empire was that, lacking zero, > >they had no way to indicate normal termination of their C programs." > >=09=09=09=09=09- Robert Firth=20 > > > > > >