on 09-17-2013 8:15 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.