Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Unnecessary query for every content access

Description

Accessing a single file, e.g.

wget --no-proxy http://qa1-za.sakaiproject.org/access/content/group/cd3c7352-e9bd-4d02-aaee-4adbbb8ab41f/eq_7c93c4.jpg

generates 2 queries to CONTENT HOSTING, viz.:

select RESOURCE_ID from CONTENT_RESOURCE where (RESOURCE_ID = '/group/cd3c7352-e9bd-4d02-aaee-4adbbb8ab41f/eq_7c93c4.jpg')
select XML, BINARY_ENTITY from CONTENT_RESOURCE where (RESOURCE_ID = '/group/cd3c7352-e9bd-4d02-aaee-4adbbb8ab41f/eq_7c93c4.jpg')

The first query is redundant because if the RESOURCE_ID doesnt' exist in the db then the 2nd query will return an empty set which can be checked for.

As content accesses are one of the highest-frequency events in production systems, optimization here is important for performance reasons.

Activity

Show:

Earle Nietzel October 25, 2013 at 3:12 PM

First query is from:
org.sakaiproject.content.impl.BaseContentService.getHttpAccess().new HttpAccess() {...}.handleAccess(HttpServletRequest, HttpServletResponse, Reference, Collection)

// test if the given reference is a resource
if (m_storage.checkResource(refId)) <==== select RESOURCE_ID from CONTENT_RESOURCE where (RESOURCE_ID = '')
{
handleAccessResource(req, res, ref, copyrightAcceptedRefs);
return;

The second query is from:
org.sakaiproject.content.impl.BaseContentService.handleAccessResource(HttpServletRequest, HttpServletResponse, Reference, Collection<String>)

// need read permission
if (!allowGetResource(ref.getId())) <===== select XML, BINARY_ENTITY from CONTENT_RESOURCE where (RESOURCE_ID = '')
throw new EntityPermissionException(sessionManager.getCurrentSessionUserId(), AUTH_RESOURCE_READ, ref.getReference());

So the first query is check to see if a resource exists and the second is to check if the user can unlock the resource (i.e. has Permission).

The queries themselves appear to efficient and I don't think refactoring this is really going save much.

David Horwitz January 25, 2010 at 4:57 AM

MAINTANCE TEAM: Unassigned so these get reviewed by the Maintance Team

Won't Fix

Details

Priority

Affects versions

Components

Assignee

Reporter

Labels

Created October 21, 2007 at 8:42 AM
Updated April 25, 2018 at 3:18 PM
Resolved October 25, 2013 at 3:19 PM