We were having performance issues with the query assessment-portlet.www.assessment-portlet.open_asssessments, so we restructured it and it improved from 20 sec to 1-2 secs.
The change consists in moving the subquery "s" from the FROM clause to the WHERE. Changes are emphasized.
Previous query:
select * from (select cr.item_id as assessment_id, cr.title, cr.description, a.password,
a.type,
to_char(a.start_time, 'YYYY-MM-DD HH24:MI:SS') as start_time,
to_char(a.end_time, 'YYYY-MM-DD HH24:MI:SS') as end_time,
to_char(now(), 'YYYY-MM-DD HH24:MI:SS') as cur_time,
cf.package_id, p.instance_name as community_name,
sc.node_id as comm_node_id, sa.node_id as as_node_id, a.anonymous_p,
acs_permission__permission_p(a.assessment_id,:user_id,'admin') as admin_p,
(select count(*) from as_sessions s1,
cr_revisions cr1 where
s1.assessment_id=cr1.revision_id
and cr1.item_id=cr.item_id
and s1.subject_id=:user_id
and completed_datetime is null) as in_progress_p,
(select count(*) from as_sessions s1,
cr_revisions cr1 where
s1.assessment_id=cr1.revision_id
and cr1.item_id=cr.item_id
and s1.subject_id=:user_id
and completed_datetime is not null) as completed_p,
a.number_tries
from as_assessments a, cr_revisions cr, cr_items ci, cr_folders cf,
site_nodes sa, site_nodes sc, apm_packages p,
(select distinct asm.assessment_id
from as_assessment_section_map asm, as_item_section_map ism
where ism.section_id = asm.section_id
and exists (select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = asm.assessment_id
and ppm.privilege = 'read'
and ppm.party_id = :user_id)) s
where a.assessment_id = cr.revision_id
and cr.revision_id = ci.latest_revision
and ci.parent_id = cf.folder_id
and ci.publish_status = 'live'
and cf.package_id in ([join $list_of_package_ids ", "])
and sa.object_id = cf.package_id
and sc.node_id = sa.parent_id
and p.package_id = sc.object_id
and s.assessment_id = a.assessment_id
and ((a.start_time < current_timestamp and a.end_time > current_timestamp) or a.start_time is null)
order by lower(p.instance_name), lower(cr.title)
) q where (q.completed_p < q.number_tries) or (q.number_tries=0 or q.number_tries is null)
New query:
select * from (select cr.item_id as assessment_id, cr.title, cr.description, a.password,
a.type,
to_char(a.start_time, 'YYYY-MM-DD HH24:MI:SS') as start_time,
to_char(a.end_time, 'YYYY-MM-DD HH24:MI:SS') as end_time,
to_char(now(), 'YYYY-MM-DD HH24:MI:SS') as cur_time,
cf.package_id, p.instance_name as community_name,
sc.node_id as comm_node_id, sa.node_id as as_node_id, a.anonymous_p,
acs_permission__permission_p(a.assessment_id,:user_id,'admin') as admin_p,
(select count(*) from as_sessions s1,
cr_revisions cr1 where
s1.assessment_id=cr1.revision_id
and cr1.item_id=cr.item_id
and s1.subject_id=:user_id
and completed_datetime is null) as in_progress_p,
(select count(*) from as_sessions s1,
cr_revisions cr1 where
s1.assessment_id=cr1.revision_id
and cr1.item_id=cr.item_id
and s1.subject_id=:user_id
and completed_datetime is not null) as completed_p,
a.number_tries
from as_assessments a, cr_revisions cr, cr_items ci, cr_folders cf,
site_nodes sa, site_nodes sc, apm_packages p
where a.assessment_id = cr.revision_id
and cr.revision_id = ci.latest_revision
and ci.parent_id = cf.folder_id
and ci.publish_status = 'live'
and cf.package_id in ([join $list_of_package_ids ", "])
and sa.object_id = cf.package_id
and sc.node_id = sa.parent_id
and p.package_id = sc.object_id
and acs_permission__permission_p(a.assessment_id,:user_id,'read') = 't'
and ((a.start_time < current_timestamp and a.end_time > current_timestamp) or a.start_time is null)
and exists (select 1 from as_assessment_section_map asm, as_item_section_map ism
where asm.assessment_id = a.assessment_id
and ism.section_id = asm.section_id)
order by lower(p.instance_name), lower(cr.title)
) q where (q.completed_p < q.number_tries) or (q.number_tries=0 or q.number_tries is null)
I think this provides the same result and, at least in our case, with a significant performance improvement.
