Skip to Content
0
Former Member
Sep 17, 2013 at 07:15 PM

Performance Issue in Collaborators report.

23 Views

Hello Experts,

As per my business requirement I created a customized report, which includes all the collaborators assigned to the Master Agreement and Sub Agreement, it was running well in DEV and QA when we moved it to PROD it was erroring out.

here is the query and results displayed

SELECT DISTINCT <%RESULTS%>

FROM

<%SCHEMA%>.FCI_DOC_COLLABORATOR T1, <%SCHEMA%>.FCI_CONTRACT T4 LEFT OUTER JOIN <%EXT_TABLE(contracts.Contract)%> T7 ON (T4.OBJECTID = T7.PARENT_OBJECT_ID), <%SCHEMA%>.FCI_UPP_USER_ACCOUNT T5, <%SCHEMA%>.FCI_QUERY_GROUP T6

WHERE

T1.PARENT_OBJECT_ID=T4.OBJECTID

AND T4.INACTIVE<>1

AND ((T1.USER_NAME_OBJECT_ID = T5.OBJECTID AND T5.INACTIVE = 0)

OR (T1.USER_GROUP_OBJECT_ID = T6.OBJECTID AND T6.INACTIVE = 0))

AND (T1.COLLABORATOR_ROLE_OBJECT_NAME<>'Owner' or (T1.COLLABORATOR_ROLE_OBJECT_NAME='Owner' AND T4.DOC_OWNER_USER_OBJECT_ID=T1.USER_NAME_OBJECT_ID))

union all

SELECT DISTINCT <%RESULTS%>

FROM

<%SCHEMA%>.FCI_DOC_COLLABORATOR T1, <%SCHEMA%>.FCI_AGREEMENT T4 LEFT OUTER JOIN <%EXT_TABLE(contracts.Agreement)%> T7 ON (T4.OBJECTID = T7.PARENT_OBJECT_ID), <%SCHEMA%>.FCI_UPP_USER_ACCOUNT T5, <%SCHEMA%>.FCI_QUERY_GROUP T6

WHERE

T1.PARENT_OBJECT_ID=T4.OBJECTID

AND T4.INACTIVE<>1

AND ((T1.USER_NAME_OBJECT_ID = T5.OBJECTID AND T5.INACTIVE = 0)

OR (T1.USER_GROUP_OBJECT_ID = T6.OBJECTID AND T6.INACTIVE = 0))AND (T1.COLLABORATOR_ROLE_OBJECT_NAME<>'Owner' or (T1.COLLABORATOR_ROLE_OBJECT_NAME='Owner' AND T4.DOC_OWNER_USER_OBJECT_ID=T1.USER_NAME_OBJECT_ID))

<%ORDERBY%>

Can you guys please look at my query and help me out resolving the issue.

Thanks,

Roopesh Vardhan

Attachments

Vinay 123.PNG (28.1 kB)