Hi!
We have modified the select open_assessments getting very better performance.
We had 15 seconds, now we get 2 seconds.
Regards,
Agustin
-------------------------------------------------
select oa.*,
acs_permission__permission_p(oa.assessment_id,:user_id,'admin') as admin_p,
acs_permission__permission_p(oa.assessment_id,:user_id,'read') as read_p
from
(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,
(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 a.assessment_id in (select distinct asm.assessment_id from as_assessment_section_map asm, as_item_section_map ism
where ism.section_id = asm.section_id)
and ((a.start_time < current_timestamp and a.end_time > current_timestamp) or a.start_time is null or a.end_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))) AS oa
where acs_permission__permission_p(oa.assessment_id,:user_id,'read') = 't'
