Sage-Bionetworks/Synapse-Repository-Services — pull request #1603 by marcel-blonk (2014-11-11 18:39:31+00:00)
Title: fix sql statement for performance

Before:

select
n.ID, n.NAME, n.NODE_TYPE
from
(select distinct
n.PROJECT_ID
from
JDONODE n
where
n.BENEFACTOR_ID in (select
acl.OWNER_ID ID
from
ACL acl, JDORESOURCEACCESS ra, JDORESOURCEACCESS_ACCESSTYPE at
where
(ra.GROUP_ID in (273949 , 2223475, 273948, 273957))
and acl.ID = ra.OWNER_ID
and acl.OWNER_TYPE = 'ENTITY'
and at.ID_OID = ra.ID
and at.STRING_ELE = 'READ')
and n.PROJECT_ID is not null) pids
join
JDONODE n ON n.ID = pids.PROJECT_ID
left join
PROJECT_STAT ps ON n.ID = ps.PROJECT_ID
and ps.USER_ID = '2223475'
order by coalesce(ps.LAST_ACCESSED, n.CREATED_ON) DESC
limit 20 offset 0;

After:

select n.ID, n.NAME, n.NODE_TYPE
from
(
select distinct n.PROJECT_ID
from
(
select acl.OWNER_ID ID
from ACL acl, JDORESOURCEACCESS ra, JDORESOURCEACCESS_ACCESSTYPE at
where
(ra.GROUP_ID in (273949 , 2223475, 273948, 273957))
and acl.ID = ra.OWNER_ID
and acl.OWNER_TYPE = 'ENTITY'
and at.ID_OID = ra.ID
and at.STRING_ELE = 'READ'
) acls
join JDONODE n on n.BENEFACTOR_ID = acls.ID
where
n.PROJECT_ID is not null
) pids
join JDONODE n ON n.ID = pids.PROJECT_ID
left join PROJECT_STAT ps ON n.ID = ps.PROJECT_ID and ps.USER_ID = '2223475'
where n.BENEFACTOR_ID in (select
acl.OWNER_ID ID
from
ACL acl, JDORESOURCEACCESS ra, JDORESOURCEACCESS_ACCESSTYPE at
where
(ra.GROUP_ID in (273949 , 2223475, 273948, 273957))
and acl.ID = ra.OWNER_ID
and acl.OWNER_TYPE = 'ENTITY'
and at.ID_OID = ra.ID
and at.STRING_ELE = 'READ')
order by coalesce(ps.LAST_ACCESSED, n.CREATED_ON) DESC
limit 20 offset 0;