CS: Performance

Symptoms:
When you have a large data set in the repository, the object_name clause (‘and object_name=’) in the folder query generated by the IDfSession.getFolderByPath API produces very slow SQL on Content Server.

Cause:
The large number and depth of folders leads to poor execution performance of folder query SQL.

Resolution:
Configure DFC by copying the following two properties from dfcfull.properties to dfc.properties and set appropriate values:

  • dfc.query.object_name_for_docbase: Name of the repository in which object_name clause needs to be included or not in the folder query present in IDfSession.getFolderByPath API.

    Specify the repository you are working with.

  • dfc.query.should_include_object_name: This value is used when running a folder query through the IDfSession.getFolderByPath API. It determines whether to include or not object_name clause in the query for the docbase specified in dfc.query.object_name_docbase. If it is explicitly mentioned as false, the object_name clause will not be included in the query for the docbase name that is mentioned in dfc.query.object_name_docbase.
    Default value is true.

    Set it to false to alleviate performance.

If there are multiple docbases to which you want to disable this object_name qualifier, you have to enter it in the following way in dfc.properties:

dfc.query.object_name_for_docbase[0]=<docbase_name1>
dfc.query.should_include_object_name[0]=false
dfc.query.object_name_for_docbase[1]=<docbase_name2>
dfc.query.should_include_object_name[1]=false

CS: enable SQL Trace

Commands to Enable / Disable sqltrace for a particular user:
apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,T,USERNAME,S,<user name>
apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,F,USERNAME,S,<user name>

Commands to Enable / Disable sqltrace for a particular session:
apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,T,SESSION_ID,S,<session id>
apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,F,SESSION_ID,S,<session id>

Example: steps performed to enable sqltrace for a user <testuser1>
Connected to IAPI via installation owner and fired below statement:
apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,T,USERNAME,S,testuser1

Later connected to IDQL as testuser1 and ran the following DQL:
select r_object_id, object_name from dm_document where r_creation_date >= date(today);

Later connected to IAPI as installation owner and turned off sqltrace
apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,F,USERNAME,S,testuser1