Forum OpenACS Development: Re: Very slow query

Collapse
3: Re: Very slow query (response to 1)
Posted by Dirk Gomez on

Andrei, I think your query is not semantically identical to Janine's query. (Actually I am pretty sure)

I tried temporary tables with amazing success on a loaded system. Can you try Janine?

Firstly create the two temporary tables:


create global temporary table object_id_tmp
  (object_id number)
  on commit delete rows ;
create global temporary table ancestor_id_tmp
  (ancestor_id number)
  on commit delete rows ;

Then replace the offending SQL query with

insert into object_id_tmp select object_id
   from acs_object_context_index
   where ancestor_id = :object_id and object_id != ancestor_id;
insert into ancestor_id_tmp select ancestor_id
   from acs_object_context_index aoci2
   where object_id = :object_id and object_id != ancestor_id;

 delete from acs_object_context_index 
  where object_id in (select object_id from object_id_tmp) and ancestor_id in (select ancestor_id from ancestor_id_tmp);

Runs very fast in the lab - loaded data but no concurrent users - and looks correct.

Collapse
8: Re: Very slow query (response to 3)
Posted by Andrei Popov on

Dirk,

You may be right, but it looked to be the same at first glance. Given that acs_object_context_index is actually

create table acs_object_context_index (
	object_id	integer not null
                        constraint acs_obj_context_idx_obj_id_fk
			references acs_objects(object_id),
	ancestor_id	integer not null
                        constraint acs_obj_context_idx_anc_id_fk
			references acs_objects(object_id),
	n_generations	integer not null
			constraint acs_obj_context_idx_n_gen_ck
			check (n_generations >= 0),
        constraint acs_object_context_index_pk
	primary key (object_id, ancestor_id)
);
and acs_object_contexts is
create view acs_object_contexts
as select object_id, ancestor_id, n_generations
   from acs_object_context_index
   where object_id != ancestor_id;
i.e. it is a view at acs_object_context_index where object_id and ancestor_id are not the same, doing a delete on acs_object_context_index for specified object_id/ancestor_id excluding the case when they are the same should do the trick, shouldn't it?