How to improve report performance

Hi,
I have created reports in CRXI with Dynamic & Cascading Prompt. My reports are taking long time 15-20 mins to refressh.
Can sombdy help me to improve my reports performance ?Can Sombdy help me?|||Can anybdy answer this question|||how many records is it selecting? it shows you in the bottom right corner|||When I run the foolowing Query. It runs fine & gives 199 records

SELECT distinct clients.uid,max(da_answer.date_effective) as date,entry_exit.provider_id as provider,
datediff(yyyy,da_answer_val_unsec.val_date,getdate()) as age,
convert(varchar,DATEDIFF ( yyyy,val_date, getdate())) vage,
Case
when DATEDIFF ( yyyy,val_date, getdate()) between 5 and 12 then '5-12'
when datediff(yyyy,val_date,getdate()) between 13 and 17 then '13-17'
when datediff(yyyy,val_date,getdate()) between 18 and 44 then '18-44'
when datediff(yyyy,val_date,getdate()) between 45 and 64 then '45-64'
else 'Over 65'
end as Agegroup

FROM (((clients INNER JOIN da_answer ON clients.uid=da_answer.client_id) INNER JOIN
(da_assessment_question INNER JOIN da_question ON
da_assessment_question.question_id=da_question.uid) ON
da_answer.question_id=da_question.uid) INNER JOIN entry_exit ON
da_answer.client_id=entry_exit.client_id) INNER JOIN da_answer_val_unsec ON da_answer.uid=da_answer_val_unsec.answer_id

WHERE da_answer.date_effective IN( select date_effective FROM da_answer a
WHERE date_effective = (select max(b.date_effective)
FROM da_answer b WHERE b.uid=a.uid) and da_question.uid=893 AND clients.inactive=0 AND
da_assessment_question.assessment_id=21 AND entry_exit.inactive=0)
AND
(((entry_exit.entry_date IS NOT NULL)
AND
(entry_exit.entry_date>={ts '2005-01-01 00:00:00'} AND
entry_exit.entry_date<={ts '2005-03-31 23:59:00'})) and
((entry_exit.exit_date is null) or not
((entry_exit.exit_date >{ts '2004-07-01 00:00:00'} and entry_exit.exit_date <{ts
'2004-12-31 23:59:00'}))))
group by clients.uid,da_answer_val_unsec.val_date,entry_exit.provider_id

After this query I got the clients who have same date_effective but I need distinct clients with max(date_effective) so I create the above query as view(AGE) & applied the following query to this view

select a.uid,a.agegroup,a.provider
from age a
where date in(select max(b.date)
from age b
where a.uid=b.uid
group by uid)
order by a.uid
This query takes 10-15mins to run in sql designer & gives 192 records.|||Have you analysed the execution of your query. It seems that the query optimizer in your DBMS does not work properly when you make the query using the view. Check the plan and make sure query uses indexes and does not perform full table joins.

- Jukka|||Hi

My performance problem is caused by ineffiecient generated HTML - for each item in the reports' table a <DIV><TABLE><TR><TD><TABLE><TR><TD> is generated (??!!) hard to believe !!

Multiply this by a few thousand items to be displayed and you've got a catastrophe.

How can I work around this ??

Thanks

David|||The HTML generated by Crystal is really ugly. We have worked around it by using the pdf format.