Forum OpenACS Q&A: Re: longdb 5 seconds query in assessment portlet

Collapse
8: Re: longdb 5 seconds query in assessment portlet (response to 1)
Posted by Don Baccus on 02/17/08 05:32 PM
I can see that you are ordering by stuff which can't have an index, like lower(p.instance_name). This is never going to be fast.
Not true at all. PG has supported functional indexes forever. It used to be an Oracle Enterprise feature but I believe that now it's even in Oracle Express.

That doesn't mean that the right index actually exists, of course, but it's certainly possible to make one. Just say "lower(column_name)" in the create index statement where you'd normally say "column_name".

Collapse
10: Re: longdb 5 seconds query in assessment portlet (response to 8)
Posted by Tom Jackson on 02/17/08 07:07 PM
That doesn't mean that the right index actually exists, of course, but it's certainly possible to make one. Just say "lower(column_name)" in the create index statement where you'd normally say "column_name".

Yes, I made a pretty inexact statement, I assume that if someone wrote such a monster query without using views to simplify stuff, they probably also skipped creating any necessary indexes.

Of course the biggest problem is that it isn't obvious exactly what the query is intended to do, making it hard to change.

Like you said, does the query return 1000 rows without the permission check? Who knows, but I doubt that substituting Tcl code for something that can be done in the database will be faster. It could easily be orders of magnitude slower.

Is there any performance effect if you use count(*) as opposed to something like count(object_id)?

Collapse
11: Re: Res: Re: longdb 5 seconds query in assessment portlet (response to 10)
Posted by Jose Agustin Lopez Bueno on 07/29/08 01:29 PM
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'