[Building Sakai] Proposed API change to reduce cost of resources list view

[Holladay, Bryan Andrew]

Another approach you could take is to look up the entire list of items in one sql call instead of separating them into multiple calls by using an "in" clause.  This will reduce the number of connections and calls to just 1 (unless there are over 1000 id's, in which case you need to break the sql up) which significantly improves performance when dealing with a large number of equal queries and avoids wildcard searches.  I haven't looked into the code, but I am making the assumption that it relies on the resources's id to find the permission.

Bryan

On 9/22/09 12:58 PM, "Jim Eng" <jimeng at umich.edu> wrote:

The list view in the Resources tool in Sakai 2.x has a column to show
who can see each resource or folder. Potential values include the
entire site, members of particular groups, and the public.

Items at any level may be made public, so the question ("is this item
public?") must be asked for virtually every item in a site.
Determining whether resources and folders are open to the public is
relatively expensive because it must be repeated so often.  If a site
contains lots of resources, the expanded view can results in dozens,
hundreds or thousands of queries.  The only short cut at this time is
that the query can be avoided for items that are inside a folder that
is public since they will also be public.

The SQL query is against the various realms tables.  It asks whether
the anonymous role has the "content.read" permission for the realm
associated with a particular resource or folder (or for the realm of
any folder that contains the item).

We could improve on that if the SecurityService (or
AuthzGroupService?) interface had a method to ask a different
question: Are there any realms within this site for which the
anonymous role has "content.read" permission (and if so, what are they)?

This query would likely be implemented with a SQL "like" operator and
a one-sided wildcard. This is actually a join of information from
three or four tables, but the query would be asking for a list of
sakai_realm.realm_id values like '/content/group/<side-id>/%' (or like
'/content/group-user/<user-id>/%' for workspaces, and it's possible
there are other variations) for which there are entries in the
sakai_realm_rl_fn table where the role_key identifies the anonymous
role and the function_key corresponds to "content.read". The result
would be a list of realm identifiers.

That would allow Content Hosting to get a list of realms (possibly
empty) for items that are public.  Content Hosting could then avoid
making dozens or hundreds or thousands of individual queries by
checking that list of public realms.  An alternative to adding a
specific API method for this would be to execute that query in the
SecurityService and do some form of caching so the Security Service
could answer this question hundreds of times without doing hundreds of
individual queries.

Thoughts?

Jim

_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090922/83563fb4/attachment.html