Help optimizing query (newbie)
Hi.
I am trying to run the following statements on a rather huge database.
==
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 wherechk_count=-1);
delete from tracked_ids where chk_count=-1;
commit work;
==
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'l
--
I started out with nothing & still have most of it left.
Frank T. Rambøl wrote:
> Hi.
>
> I am trying to run the following statements on a rather huge database.
>
> ==
> 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 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øl
>
> --
> I started out with nothing & still have most of it left.
Hi !
I guess your problem maybe trimming those strings (image_id[16,26]).
Try something like:
create temp_strings (id char);
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