Re: Help optimizing query (newbie)
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