cancel
Showing results for 
Search instead for 
Did you mean: 

Performance Issue in Collaborators report.

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hi Roopesh,

What is the error message you are getting?

FCI_DOC_COLLABORATOR table is used by all the business documents and as a result this table tends to get  pretty big. It is important to use the right join conditions.

I would do the join as follows for the MA select statement:

FCI_DOC_LINK.PARENT_CLASS_ID = 1004 AND FCI_DOC_LINK.PARENT_OBJECT_ID = T4.OBJECTID

Similarly for Sub Agreement I would do…

FCI_DOC_LINK.PARENT_CLASS_ID = 1003 AND FCI_DOC_LINK.PARENT_OBJECT_ID = T4.OBJECTID

Regards,

Vikram

Former Member
0 Kudos

Thanks for response.

Here is the query:

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))

If you see the above query, we use a lot of conditions related to FCI_DOC_COLLABORATOR (T1) table fields. If we don't use that, how we gonna impose the above filters used for T1.

The query works fine, it's just performance problem in production; it takes more than 10 min and then time out. I will appreciate your prompt response.

Thanks

Roopesh

0 Kudos

Hi Roopesh,

That’s not really not what I meant. Sorry, if I wasn't clear.

As I mentioned earlier, FCI_DOC_COLLABORATOR table is a common table used by all business documents and tend to be very big. So, your query needs to be as efficient as possible. So what I meant was the where conditions I mentioned previously should be part of your query in addition to other where conditions.

Regards,

Vikram

Former Member
0 Kudos

Hi Roopesh,

   Please try the below query. Hope this will improve the report performance.

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)

 

WHERE

T1.PARENT_OBJECT_ID=T4.OBJECTID

AND T4.INACTIVE<>1

AND (EXISTS(SELECT 1 FROM ESOURCE.FCI_UPP_USER_ACCOUNT T5 WHERE T1.USER_NAME_OBJECT_ID = T5.OBJECTID AND T5.INACTIVE = 0)

          OR EXISTS(SELECT 1 FROM ESOURCE.FCI_QUERY_GROUP T6 WHERE 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)

WHERE

T1.PARENT_OBJECT_ID=T4.OBJECTID

AND T4.INACTIVE<>1

AND (EXISTS(SELECT 1 FROM ESOURCE.FCI_UPP_USER_ACCOUNT T5 WHERE T1.USER_NAME_OBJECT_ID = T5.OBJECTID AND T5.INACTIVE = 0)

          OR EXISTS(SELECT 1 FROM ESOURCE.FCI_QUERY_GROUP T6 WHERE 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%>

Thanks

Swastik

Former Member
0 Kudos

Thanks swastik and Vikram for reply,

Swastik,

I used your query it did improve the performance,but when I tested it I saw the inactive groups in the report and the active owner of the document was missing.I can see the Inactive owner in few of the documents,

Can you please suggest.......

Thanks,

Roopesh.

Answers (0)