Working with Queries

Constructing a SQL Statement

The SearchSQL class provides helper methods to construct the SQL statement, or you can pass an existing SQL statement in to a SearchSQL instance. The helper methods are supplied for assistance in building SQL statements, and cannot provide the level of specification you can achieve with an independently-constructed statement.

Note: You cannot combine these two manners of construction. The SQL statement must be specified in its entirety using either the SearchSQL helper methods or an independently-constructed statement. However, for the purposes of development, you can use the helper methods to build the SQL statement, then use the SearchSQL.toString method to get the SQL statement string and further refine the SQL statement manually before passing it to SearchSQL.setQueryString.

Using the SearchSQL Methods

The general sequence for constructing a SQL statement using the SearchSQL methods is as follows:

Java™ Example

    // Create the SearchSQL object.
    SearchSQL sqlObject = new SearchSQL();

    // (Optional) Specify the maximum number of records to be returned. This defaults to the
    // value of ServerCacheConfiguration.NonPagedQueryMaxSize, if unspecified.
    sqlObject.setMaxRecords(150);

    // Specify the SELECT list using the setSelectList method.
    String select = "r.Title, s.Title";
    sqlObject.setSelectList(select);

    // Specify the FROM clause using the setFromClauseInitialValue method.
    // Symbolic name of class.
    String myClassName1 = "Requirement";
    // Alias name.
    String myAlias1 = "r";
    // Indicates whether subclasses are included.
    boolean subclassesToo = false;
    sqlObject.setFromClauseInitialValue(myClassName1, myAlias1, subclassesToo);

    // For joins, specify an additional FROM clause using the setFromClauseAdditionalJoin 
    // method. Symbolic name of class.
    String myClassName2 = "Specification";
    // Alias name.
    String myAlias2 = "s";
    // The property on the class specified for the setFromClauseInitialValue 
    // method. This is one constituent in the underlying ON clause. The other constituent is 
    // prop2.
    String prop1 = "s.ApplicationName";
    // The property on the class specified for this method (myClassName2). This is the other 
    // constituent of the underlying ON clause for the join.
    String prop2 = "r.ApplicationName";
    // Indicates whether subclasses are included.
    subclassesToo = false;
    sqlObject.setFromClauseAdditionalJoin(
        JoinOperator.INNER, myClassName2, myAlias2, prop1, 
        JoinComparison.EQUAL, prop2, subclassesToo);

    // Specify the WHERE clause using the setWhereClause method.
    String whereClause = "r.Title LIKE '%P8%'";
    sqlObject.setWhereClause(whereClause);

    // Specify the ORDER BY clause using the setOrderByClause method.
    String orderClause = "r.Title";
    sqlObject.setOrderByClause(orderClause);

    // Check the SQL statement constructed.
    System.out.println("SQL: " + sqlObject.toString());

    // Create a SearchScope instance and test the SQL statement.
    SearchScope searchScope = new SearchScope(os);
    // Uses fetchRows to test the SQL statement.
    RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new Boolean(true));

C# Example

    // Create the SearchSQL object.
    SearchSQL sqlObject = new SearchSQL();

    // (Optional) Specify the maximum number of records to be returned. This defaults to the
    // value of ServerCacheConfiguration.NonPagedQueryMaxSize, if unspecified.
    sqlObject.SetMaxRecords(150);

    // Specify the SELECT list using the setSelectList method.
    string select = "r.Title, s.Title";
    sqlObject.SetSelectList(select);

    // Specify the FROM clause using the setFromClauseInitialValue method.
    // Symbolic name of class.
    string myClassName1 = "Requirement";
    // Alias name.
    string myAlias1 = "r";
    // Indicates whether subclasses are included.
    bool subclassesToo = false;
    sqlObject.SetFromClauseInitialValue(myClassName1, myAlias1, subclassesToo);

    // For joins, specify an additional FROM clause using the setFromClauseAdditionalJoin 
    // method. Symbolic name of class.
    string myClassName2 = "Specification";
    // Alias name.
    string myAlias2 = "s";
    // The property on the class specified for the setFromClauseInitialValue 
    // method. This is one constituent in the underlying ON clause. The other constituent is 
    // prop2.
    string prop1 = "s.ApplicationName";
    // The property on the class specified for this method (myClassName2). This is the other 
    // constituent of the underlying ON clause for the join.
    string prop2 = "r.ApplicationName";
    // Indicates whether subclasses are included.
    subclassesToo = false;
    sqlObject.SetFromClauseAdditionalJoin(
        JoinOperator.INNER, myClassName2, myAlias2, prop1, 
        JoinComparison.EQUAL, prop2, subclassesToo);

    // Specify the WHERE clause using the setWhereClause method.
    string whereClause = "r.Title LIKE '%P8%'";
    sqlObject.SetWhereClause(whereClause);

    // Specify the ORDER BY clause using the setOrderByClause method.
    string orderClause = "r.Title";
    sqlObject.SetOrderByClause(orderClause);

    // Check the SQL statement constructed.
    System.Console.WriteLine("SQL: " + sqlObject.ToString());

    // Create a SearchScope instance and test the SQL statement.
    SearchScope searchScope = new SearchScope(os);
    // Uses fetchRows to test the SQL statement.
    IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);

Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.

The SearchSQL instance (sqlObject) can then be specified in the SearchScope parameter list to execute the search. See SQL Syntax Reference for detailed information about SQL statement syntax.

Using an Independently-Constructed Statement

A SQL statement that conforms to IBM FileNet standards can be passed to SearchSQL in string format. You can use either the constructor SearchSQL(String), or the setQueryString method.

Java Example

    // The SQL statement "SELECT DocumentTitle Id FROM Document WHERE DocumentTitle LIKE 
    // '%Acct%'" retrieves documents by ID (GUID) that have a document 
    // title containing the character pattern "Acct".
    // You could store this statement in a String variable and pass it to the 
    // setQueryString method, similar to this:
    String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE DocumentTitle LIKE '%Acct%'";
    SearchSQL sqlObject = new SearchSQL();
    sqlObject.setQueryString(mySQLString);

    // Alternatively, you could use the SearchSQL(String) constructor:
    // String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE DocumentTitle 
    // LIKE '%Acct%'";
    // SearchSQL sqlObject = new SearchSQL(mySQLString);
    
    // The SearchSQL instance (sqlObject) can then be specified in the 
    // SearchScope parameter list to execute the search. Uses fetchRows to test the SQL 
    // statement.
    SearchScope searchScope = new SearchScope(os);
    RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new Boolean(true));

C# Example

    // The SQL statement "SELECT DocumentTitle Id FROM Document WHERE DocumentTitle LIKE 
    // '%Acct%'" retrieves documents by ID (GUID) that have a document 
    // title containing the character pattern "Acct".
    // You could store this statement in a String variable and pass it to the 
    // SetQueryString method, similar to this:
    String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE DocumentTitle LIKE '%Acct%'";
    SearchSQL sqlObject = new SearchSQL();
    sqlObject.SetQueryString(mySQLString);

    // The SearchSQL instance (sqlObject) can then be specified in the 
    // SearchScope parameter list to execute the search. Uses fetchRows to test the SQL 
    // statement.
    SearchScope searchScope = new SearchScope(os);
    IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);

Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.

See SQL Syntax Reference for detailed information about SQL statement syntax.

Searching for Content

You can include content searches (full-text searches) in queries constructed using either the SearchSQL helper methods or independently-constructed SQL statements. The CONTAINS and FREETEXT operators perform the content search functions. CONTAINS is used for explicit or fuzzy full-text queries, and can search for content in all CBR-enabled properties on the object, or a single CBR-enabled property. The FREETEXT function is used for inexact, semantic-based full-text queries, and searches all CBR-enabled properties on the object only, so the first argument is always "*". The second argument is a filter expression.

See Content Searches and Full-Text Queries for more information.

Content Searches using the SearchSQL Methods

To specify a content search with the SearchSQL helper methods, use setContainsRestriction and setFreetextRestriction. The following example uses setContainsRestriction to search for the specified content in all IsCBREnabled properties of the Document class:

Note: The setContainsRestriction helper method does not provide the same level of functionality as the CONTAINS operator can in an independently-constructed SQL statement. The setContainsRestriction method does not accept a property parameter to constrain the full-text search to a specific property.

Java Example

    SearchSQL sqlObject = new SearchSQL();
            
    String myClassName = "Document";
    sqlObject.setSelectList("DocumentTitle");
    sqlObject.setFromClauseInitialValue(myClassName, null, false);

    String containsExpression = "'FileNet'";
    sqlObject.setContainsRestriction(myClassName, containsExpression);

    // Displays the SQL statement.
    System.out.println("SQL: " + sqlObject.toString());
    
    // Executes the content search.
    SearchScope searchScope = new SearchScope(os);            
    RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new Boolean(true)); 

C# Example

    SearchSQL sqlObject = new SearchSQL();

    string myClassName = "Document";
    sqlObject.SetSelectList("DocumentTitle");
    sqlObject.SetFromClauseInitialValue(myClassName, null, false);

    string containsExpression = "'FileNet'";
    sqlObject.SetContainsRestriction(myClassName, containsExpression);

    // Displays the SQL statement.
    System.Console.WriteLine("SQL: " + sqlObject.ToString());

    // Executes the content search.
    SearchScope searchScope = new SearchScope(os);            
    IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);

Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.

Content Searches using Independently-Constructed Statements

To specify a content search in an independently-constructed SQL statement, add the CONTAINS or FREETEXT functions within the WHERE clause. Only one CONTAINS or FREETEXT function (and not both) can be used in a single SQL statement.

The following examples illustrate the different ways these content searches can be constructed:

Single Property Search

In this example, only content in the AccountName property will be searched (assuming IsCBREnabled is true for this property).

Java Example

    String mySQLString = "SELECT DocumentTitle Id FROM Document d "
        + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject "
        + "WHERE CONTAINS(Name, 'FileNet')"; 
        
    SearchSQL sqlObject = new SearchSQL(mySQLString);
    
    // Executes the content search.
    SearchScope searchScope = new SearchScope(os);            
    RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new Boolean(true));

C# Example

    String mySQLString = "SELECT DocumentTitle Id FROM Document d "
        + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject "
        + "WHERE CONTAINS(Name, 'FileNet')";

    SearchSQL sqlObject = new SearchSQL(mySQLString);

    // Executes the content search.
    SearchScope searchScope = new SearchScope(os);            
    IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);

All Properties Search

In this example, the content of all properties on the object will be searched for a semantic correlation to "company" (assuming IsCBREnabled is true for all of these properties).

Java Example

    String mySQLString = "SELECT DocumentTitle Id FROM Document d " 
        + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject " 
        + "WHERE FREETEXT(*, 'company')";
        
    SearchSQL sqlObject = new SearchSQL(mySQLString);
    
    // Executes the content search.
    SearchScope searchScope = new SearchScope(os);            
    RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new Boolean(true));

C# Example

    String mySQLString = "SELECT DocumentTitle Id FROM Document d " 
        + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject " 
        + "WHERE FREETEXT(*, 'company')";
        
    SearchSQL sqlObject = new SearchSQL(mySQLString);
    
    // Executes the content search.
    SearchScope searchScope = new SearchScope(os);            
    IRepositoryRowSet rowSet =  searchScope.FetchRows(sqlObject, null, null, true);

Searching Multiple Repositories

Any of the SearchScope methods can be used to search multiple repositories. The general sequence for specifying multiple repositories is as follows:

Java Example

    // Create the ObjectStore array required as the initial SearchScope 
    // parameter. (Assumes you have the object store objects.)
    ObjectStore[] osArray = new ObjectStore[]{os1,os2};

    // Create the SearchScope instance using the constructor for multiple object 
    // stores, and specifying the merge mode.
    SearchScope searchMultiple = new SearchScope(osArray, MergeMode.INTERSECTION);

C# Example

    // Create the ObjectStore array required as the initial SearchScope 
    // parameter. (Assumes you have the object store objects.)
    IObjectStore[] osArray = new IObjectStore[]{os1,os2};

    SearchScope searchMultiple = new SearchScope(osArray, MergeMode.INTERSECTION);

See Merge Mode for more information about merge mode settings.

Refer to Searching Multiple Object Stores for more information.

Searching for Objects

Use the SearchScope.fetchObjects method to search for objects in a repository. The fetchObjects method returns a collection of IndependentObject objects. The general sequence for performing a query for objects is as follows:

Java Example

    // Create a SearchSQL instance and specify the SQL statement (using the 
    // helper methods).
    SearchSQL sqlObject = new SearchSQL();
    sqlObject.setSelectList("d.DocumentTitle, d.Id");
    sqlObject.setMaxRecords(20);
    sqlObject.setFromClauseInitialValue("Document", "d", false);  
        
    // Check the SQL statement.          
    System.out.println("SQL: " + sqlObject.toString()); 

    // Create a SearchScope instance. (Assumes you have the object store 
    // object.)
    SearchScope search = new SearchScope(os);

    // Set the page size (Long) to use for a page of query result data. This value is passed 
    // in the pageSize parameter. If null, this defaults to the value of 
    // ServerCacheConfiguration.QueryPageDefaultSize.
    Integer myPageSize = new Integer(100);

    // Specify a property filter to use for the filter parameter, if needed. 
    // This can be null if you are not filtering properties.
    PropertyFilter myFilter = new PropertyFilter();
    int myFilterLevel = 1;
    myFilter.setMaxRecursion(myFilterLevel);
    myFilter.addIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null)); 
        
    // Set the (Boolean) value for the continuable parameter. This indicates 
    // whether to iterate requests for subsequent pages of result data when the end of the 
    // first page of results is reached. If null or false, only a single page of results is 
    // returned.
    Boolean continuable = new Boolean(true);

    // Execute the fetchObjects method using the specified parameters.
    IndependentObjectSet myObjects = search.fetchObjects(sqlObject, myPageSize, myFilter, continuable);

C# Example

    // Create a SearchSQL instance and specify the SQL statement (using the 
    // helper methods).
    SearchSQL sqlObject = new SearchSQL();
    sqlObject.SetSelectList("d.DocumentTitle, d.Id");
    sqlObject.SetMaxRecords(20);
    sqlObject.SetFromClauseInitialValue("Document", "d", false);

    // Check the SQL statement.          
    System.Console.WriteLine("SQL: " + sqlObject.ToString());

    // Create a SearchScope instance. (Assumes you have the object store 
    // object.)
    SearchScope search = new SearchScope(os);

    // Set the page size (Long) to use for a page of query result data. This value is passed 
    // in the pageSize parameter. If null, this defaults to the value of 
    // ServerCacheConfiguration.QueryPageDefaultSize.
    int myPageSize = 100;

    // Specify a property filter to use for the filter parameter, if needed. 
    // This can be null if you are not filtering properties.
    PropertyFilter myFilter = new PropertyFilter();
    int myFilterLevel = 1;
    myFilter.SetMaxRecursion(myFilterLevel);
    myFilter.AddIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null));

    // Set the (boolean) value for the continuable parameter. This indicates 
    // whether to iterate requests for subsequent pages of result data when the end of the 
    // first page of results is reached. If null or false, only a single page of results is 
    // returned.
    bool continuable = true;

    // Execute the fetchObjects method using the specified parameters.
    IIndependentObjectSet myObjects = search.FetchObjects(sqlObject, myPageSize, myFilter, continuable);

See Constructing a SQL Statement for the general sequence and examples.

See Searching Multiple Repositories for the sequence and examples when searching multiple object stores.

Searching for Database Rows

Use the SearchScope.fetchRows method to search rows in the Content Engine database. The fetchRows method returns a collection of RepositoryRow objects. The general sequence for performing a query for database rows is as follows:

Java Example

    // Create a SearchSQL instance and specify the SQL statement (using the helper methods).
    SearchSQL sqlObject = new SearchSQL();
    sqlObject.setSelectList("d.DocumentTitle, d.Id");
    sqlObject.setMaxRecords(20);
    sqlObject.setFromClauseInitialValue("Document", "d", false);  
    
    // Check the SQL statement.          
    System.out.println("SQL: " + sqlObject.toString()); 

    // Create a SearchScope instance. (Assumes you have the object store object.)
    SearchScope search = new SearchScope(os);

    // Set the page size (Long) to use for a page of query result data. This value is passed 
    // in the pageSize parameter. If null, this defaults to the value of 
    // ServerCacheConfiguration.QueryPageDefaultSize.
    Integer myPageSize = new Integer(100);

    // Specify a property filter to use for the filter parameter, if needed. 
    // This can be null if you are not filtering properties.
    PropertyFilter myFilter = new PropertyFilter();
    int myFilterLevel = 1;
    myFilter.setMaxRecursion(myFilterLevel);
    myFilter.addIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null)); 

    // Set the (Boolean) value for the continuable parameter. This indicates 
    // whether to iterate requests for subsequent pages of result data.
    Boolean continuable = new Boolean(true);

    // Execute the fetchRows method using the specified parameters.
    RepositoryRowSet myRows = search.fetchRows(sqlObject, myPageSize, myFilter, continuable);

    // You can then iterate through the collection of rows to access the properties.
    int rowCount = 0;
    Iterator iter = myRows.iterator();
    while (iter.hasNext()) {
        RepositoryRow row = (RepositoryRow) iter.next();
            
        String docTitle = row.getProperties().get("DocumentTitle").getStringValue();
        Id docId = row.getProperties().get("Id").getIdValue();
            
        rowCount++;
        System.out.print(" row " + rowCount + ":");
        System.out.print(" Id=" + docId.toString());
        if (docTitle != null) {
            System.out.print(" DocumentTitle=" + docTitle);
        }
        System.out.println();                                    
    }            

C# Example

    // Create a SearchSQL instance and specify the SQL statement (using the helper methods).
    SearchSQL sqlObject = new SearchSQL();
    sqlObject.SetSelectList("d.DocumentTitle, d.Id");
    sqlObject.SetMaxRecords(20);
    sqlObject.SetFromClauseInitialValue("Document", "d", false);            

    // Check the SQL statement.          
    System.Console.WriteLine("SQL: " + sqlObject.ToString());

    // Create a SearchScope instance. (Assumes you have the object store object.)
    SearchScope search = new SearchScope(os);

    // Set the page size (Long) to use for a page of query result data. This value is passed 
    // in the pageSize parameter. If null, this defaults to the value of 
    // ServerCacheConfiguration.QueryPageDefaultSize.
    int myPageSize = 100;

    // Specify a property filter to use for the filter parameter, if needed. 
    // This can be null if you are not filtering properties.
    PropertyFilter myFilter = new PropertyFilter();
    int myFilterLevel = 1;
    myFilter.SetMaxRecursion(myFilterLevel);
    myFilter.AddIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null));

    // Set the (boolean) value for the continuable parameter. This indicates 
    // whether to iterate requests for subsequent pages of result data.
    bool continuable = true;

    // Execute the fetchRows method using the specified parameters.
    IRepositoryRowSet myRows = search.FetchRows(sqlObject, myPageSize, myFilter, continuable);

    // You can then iterate through the collection of rows to access the properties.
    int rowCount = 0;
    foreach(IRepositoryRow row in myRows)
    {
        string docTitle = row.Properties.GetProperty("DocumentTitle").GetStringValue();
        Id docId = row.Properties.GetProperty("Id").GetIdValue();
        
        rowCount++;
        System.Console.WriteLine(" row " + rowCount + ":");
        System.Console.WriteLine(" Id=" + docId.ToString());
        if (docTitle != null)
        {
            System.Console.WriteLine(" DocumentTitle=" + docTitle);
        }
        System.Console.WriteLine();                                    
    }

See Constructing a SQL Statement for the general sequence and examples.

See Searching Multiple Repositories for the sequence and examples.

Searching for Metadata

Use the SearchScope.fetchSearchableClassDescriptions method to search for metadata. The fetchSearchableClassDescriptions method returns a collection of ClassDescription objects. There is no SearchSQL parameter for this method. The general sequence for performing a query for metadata is as follows:

Java Example

    // Create a SearchScope instance. (Assumes you have the object store 
    // object.)
    SearchScope search = new SearchScope(os);

    // Specify the names of the classes containing the metadata you want to find. This must 
    // be a String array of the identifiers (symbolic names, display names, or object IDs).
    String[] myClassNames = new String[]{"Document", "Annotation"};
        
    // Specify a property filter to use for the filter parameter, if needed.
    // This can be null if you are not filtering properties.
    PropertyFilter myFilter = new PropertyFilter();
    int myFilterLevel = 1;
    myFilter.setMaxRecursion(myFilterLevel);
    myFilter.addIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null));

    // Execute the fetchSearchableClassDescriptions method using the specified 
    // parameters.
    ClassDescriptionSet myMetadata = search.fetchSearchableClassDescriptions(myClassNames, myFilter);

C# Example

    // Create a SearchScope instance. (Assumes you have the object store 
    // object.)
    SearchScope search = new SearchScope(os);

    // Specify the names of the classes containing the metadata you want to find. This must 
    // be a String array of the identifiers (symbolic names, display names, or object IDs).
    string[] myClassNames = new string[] { "Document", "Annotation" };

    // Specify a property filter to use for the filter parameter, if needed.
    // This can be null if you are not filtering properties.
    PropertyFilter myFilter = new PropertyFilter();
    int myFilterLevel = 1;
    myFilter.SetMaxRecursion(myFilterLevel);
    myFilter.AddIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null));

    // Execute the fetchSearchableClassDescriptions method using the specified 
    // parameters.
    IClassDescriptionSet myMetadata = search.FetchSearchableClassDescriptions(myClassNames, myFilter);

See Searching Multiple Repositories for the sequence and examples.

Best Practices for Searches

Using Query Builder

Use the Enterprise Manager's Query Builder tool to construct your query or to quickly validate that your query works as intended. Be sure to include the object reference "this" in the SELECT clause when attempting to execute any query examples (included in this documentation) in Query Builder > View > SQL View. For example, this query:

    SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc' 

must be entered into Query Builder in this form:

    SELECT d.this, d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc' 

For more information on using Query Builder, see Content Engine Query Builder

Limit Rows Returned

Setting the values of certain properties can prevent a user from allocating excessive memory when making requests to the Content Engine server—a situation that could cause the server to become slow due to memory limitations. Limit the number of rows returned from your query to a usable number, either by specifying your query's row selection criteria to achieve that result, or by deciding on that number in advance by setting the following ServerCacheConfiguration properties:

You can also set the MaxQueryTimeLimit property to a value that limits the maximum amount of time that a query for a page can consume. Like the ServerCacheConfiguration properties listed above, this ObjectStore property limits excessive server resource usage by a single user.

Avoid Non-Indexed Ordering and Searching

Avoid referencing any non-indexed column in a JOIN, WHERE, or ORDER BY clause. For example, optimize the following query by creating an index on Document.DocumentTitle (database column DocVersion.xxx_documenttitle):

    SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc' 

Note: The Document class maps to the DocVersion table.

Also, avoid WHERE clauses with the LIKE operator when the searched-for column value does not permit the effective use of indexes. For example:

    SELECT d.Id FROM Document d WHERE d.DocumentTitle LIKE '%abc' 

In this example, even if an index exists for the DocumentTitle column, the query cannot use it due to the leading wildcard ("%") in the searched-for column value. Code your query to return a relatively small number of rows by placing a sufficient number of characters before the wildcard. In search dialogs, allow end users to perform "Starting with" searches (such as for "abc%") but not "Contains" searches (such as for "%abc%").

Queries that do not make effective use of indexes can potentially cause the database to lock the table. Other users can then be blocked from updating the table for the duration of the query, causing checkins to time out, and other such problems.

Avoid Non-Function-Indexed Case-Insensitive Comparisons (Oracle/DB2)

Avoid any column value comparison resulting from a JOIN, WHERE, or ORDER BY clause for any non-indexed column, or for any column belonging to an index, that does not directly or indirectly use the LOWER function. You should follow this guideline in these circumstances:

For DB2, generate a column by applying the LOWER function to a pre-existing column, and then create an index on the generated column. For Oracle, create a function-based index. For example, the index LOWER(DocVersion.xxx_documenttitle) makes the following queries more efficient:

    SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc' 
    SELECT d.Id, d.DocumentTitle, d.Creator FROM Document d ORDER BY d.DocumentTitle 

Note: The Document class maps to the DocVersion table.

Avoid Unnecessary Object Type Searches

Avoid unnecessarily searching for subclass types by adding the EXCLUDESUBCLASSES operator to your query (a query has an implicit INCLUDESUBCLASSES operator by default).

For example, as a result of the implicit INCLUDESUBCLASSES, the following query can return objects belonging to a Document subclass, in addition to those belonging to the Document class:

    SELECT d.Id FROM Document d WHERE DocumentTitle = 'MyDoc' 

Presuming only objects from the Document class are needed, instead of writing the query as:

    SELECT d.Id FROM Document d WHERE DocumentTitle = 'MyDoc' AND ISCLASS(d, Document) 

use the EXCLUDESUBCLASSES operator in this manner:

    SELECT d.Id FROM Document d WITH EXCLUDESUBCLASSES WHERE DocumentTitle = 'MyDoc' 

See SQL Syntax Reference for more information about the EXCLUDESUBCLASSES and INCLUDESUBCLASSES operators.

Avoid Unnecessary Column Returns

Avoid returning all of the columns in a table when only some are needed. For example, instead of:

  
    SELECT d.* FROM Document d WHERE d.DocumentTitle = 'MyDoc'

specify the needed columns, as in this example:

    SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'

This minimizes the amount of data transmitted over the network. Also, when all of the columns in the SELECT clause are indexed, the data for the query might be retrieved directly from the index, making the physical row lookup unnecessary.

Avoid Complex Table Linkages

Avoid referencing three or more tables in a query. More tables can degrade query performance, and complicate performance-tuning efforts.

Avoid Unnecessary Result Row Ordering

Avoid an unnecessary ORDER BY clause. Explicit row ordering may be unnecessary when you can rely on your query returning its results in a particular order as a side-effect of an indexed search. For example, the ORDER BY clause in the following query may be unnecessary since a composite index exists for the Container table on (parent_container_id, name):

    SELECT f.FolderName FROM Folder f WHERE f.Parent = Object('/sub1/sub1a') 
        AND f.IsHiddenContainer = false ORDER BY f.FolderName 

Note: The Folder class maps to the Container table.

Avoid Subqueries (Oracle)

Avoid using subqueries and operators that indirectly generate subqueries whenever you are querying an object store that uses Oracle as the database engine. Potential subquery-related issues exist with the Oracle optimizer. Specifically, use an INNER JOIN instead of a subquery (however, see also the guideline on avoiding complex table linkages). For example, rewrite the potentially slow query:

    SELECT d.Id FROM Document d WHERE d.Id IN (SELECT b.Bp8ObjectGuid FROM Bp8Attachment b)

in this functionally equivalent form:

    SELECT d.Id FROM Document d INNER JOIN Bp8Attachment b ON d.Id = b.Bp8ObjectGuid 

Note: FileNet's Business Process Framework product uses the Bp8Attachment table.

Also, because the Content Engine uses a subquery to implement the INFOLDER operator, avoid using that operator. For example, rewrite the query:

    SELECT f.FolderName FROM Folder f WHERE f.this INFOLDER '/sub1/sub1a' AND f.IsHiddenContainer = false 

in this manner:

    SELECT f.FolderName FROM Folder f WHERE f.Parent = OBJECT('/sub1/sub1a') AND f.IsHiddenContainer = false 

Also, rewrite a query like this:

    SELECT d.id FROM Document d WHERE d.This INFOLDER '/sub1/sub1a' 

in this more efficient form:

    SELECT d.Id FROM Document d INNER JOIN ReferentialContainmentRelationship r ON d.This = r.Head 
        WHERE r.Tail = OBJECT('/sub1/sub1a') 

See SQL Syntax Reference for more information about the INFOLDER operator.