Forum OpenACS Q&A: Problem with performance Query.
with Openacs 4.0 and Postgresql 7.1.3.
My problem is the following: I have one report that have it code:
set query "select distinct visitor_id from stri_visitor_inst where
date_arrival
between to_date('$date_from','yyyy-mm-dd') and to_date
('$date_to','yyyy-mm-dd')
or date_departure between to_date('$date_from','yyyy-mm-dd') and
to_date('$date_
to','yyyy-mm-dd')"
query visitor_inst visitor_inst multirow $query
set visitor_qty [multirow size visitor_inst]
set query "select a.country_id,country_name,(select count(country_id)
from (sele
ct distinct visitor_id,institution,country_id from stri_visitor_inst
where (date
_arrival between to_date('$date_from','yyyy-mm-dd') and to_date
('$date_to','yyyy
-mm-dd') or date_departure between to_date('$date_from','yyyy-mm-dd')
and to_dat
e('$date_to','yyyy-mm-dd')) and country_id=a.country_id) as temp) as
c_qty,a.ins
titution,inst_name,(select count(institution) from (select distinct
visitor_id,i
nstitution,country_id from stri_visitor_inst where (date_arrival
between to_date
('$date_from','yyyy-mm-dd') and to_date('$date_to','yyyy-mm-dd') or
date_departu
re between to_date('$date_from','yyyy-mm-dd') and to_date
('$date_to','yyyy-mm-dd
')) and institution=a.institution) as temp) as inst_qty from
stri_institutions a
inner join stri_countries c on a.country_id = c.country_id"
query country_inst country_inst multirow $query
set query "select count(country_id) from (select distinct country_id
from (selec
t distinct visitor_id,institution,country_id from stri_visitor_inst
where (date_
arrival between to_date('$date_from','yyyy-mm-dd') and to_date
('$date_to','yyyy-
mm-dd') or date_departure between to_date('$date_from','yyyy-mm-dd')
and to_date
('$date_to','yyyy-mm-dd')) and country_id>0) as temp1) as temp"
query total_countries total_countries onevalue $query
set query "select count(institution) from (select distinct
institution from (sel
ect distinct visitor_id,institution,country_id from stri_visitor_inst
where (dat
e_arrival between to_date('$date_from','yyyy-mm-dd') and to_date
('$date_to','yyy
y-mm-dd') or date_departure between to_date('$date_from','yyyy-mm-
dd') and to_da
te('$date_to','yyyy-mm-dd')) and institution>1) as temp1) as temp"
query total_institutions total_institutions onevalue $query
------------------
And in the adp file have a code as it:
<multiple name=country_inst>
<group column=country_id>
<if @country_inst.c_qty@ ne 0>
<tr><td colspan=3><hr></td></tr>
<tr>
<td colspan=2><b><if @country_inst.country_id@ eq
0>None</if><else>@country_inst
.country_name@</else></b></td><td
align=right><b>@country_inst.c_qty@</b></td>
</tr>
When I execute this program in my system, the performace is very very
slow..... Why???
I have 3000 registers in my database.
Please help anybody!!!
Thanks
