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

Collapse
Posted by Nima Mazloumi on
Most time consuming is the priviledge check:

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 = '1234')

Taking that out the query will only take 26ms. I don't see any reason why the permission (admin_p) and privilege checks need to be included in the query. Checking in tcl is by far less consuming due to caching.

Any objections going down that alley?

Collapse
Posted by Don Baccus on
Permission caching in Tcl isn't safe because there's no comprehensive cache clearing when a permission is changed.

Also, "Checking in tcl is by far less consuming due to caching" is not, in general, a true statement. Most sites will run with permission caching turned off due to the problems caching can cause. And if a query returns (for example) 10 rows with the permission check, and 1000 rows without the permission check, doing 1000 permission checks in Tcl, one at a time, after the query, is NOT likely to be faster even IF you have caching turned on. And if you don't, doing 1000 additional queries isn't going to be fast.

Of course a lot of queries don't fit that profile, but many do, so you should be aware that your general statement about tcl checking being faster is simply wrong.

The "exists" permission check is the recommended way to do it and typically is very fast, and indeed is used throughout the toolkit. My guess is that it's blowing out the query because of the way it's being used, i.e. in a subquery.

Do what you want locally, but I don't consider moving the checking to tcl to be something we'd want committed to our CVS. Someone who uses assessment ought to work on that query, though, I'm sure it can be sped up considerably.