Speedier Lists and Searches

Fresh off the press! This information has just been updated, so I decided to post it here first!

It's about optimal performance when searching for records or running lists in Oracle CRM On Demand. So follow these best practices to minimize impact on performance.

Filter on Optimized (Indexed) Fields
Whenever possible, you want to base your list or search on indexed fields. Indexed fields are optimized so that your list results will appear more quickly.

Lists: When you run a list, you select the fields to filter on from a drop-down list. This drop-down list shows optimized fields in green text.


It is recommended that you make use of optimized fields when defining a list. You or an administrator may need to migrate the data to optimized fields if they are critical to filter or sort on.

Advanced Search: When you use Advanced Search, you have the option to select from another set of optimized fields, which are shown in blue text. Filtering on optimized fields--both case-sensitive and case-insensitive--provides faster results than searching on fields that are not optimized.


Use Equality Operators
For the best performance when searching records or running lists, use these "equality" operators for your conditions: Contains all values, Equal to


Although other operators may provide some functional flexibility, they may also hurt performance. So other operators should only be used when absolutely required.

  • Avoid using the Contains at least one value operator, which impacts performance.
  • Avoid using a leading wildcard (*), such as *ion, in any search or list as it will generate an inefficient query and may be slow.
  • If you have to use a non-equality operator, then try combining that criterion with another equality criterion on an optimized field.



    Note: Following the mentioned recommendations is even more important when using Manager Visibility, since Manager Visibility expands the number of records that the system reviews.

    Do Not Specify a Sort Order
    For best performance, do not specify a sort order. Instead, let the system choose the order to sort on.

    If you have to specify the sort order, choose the same field that you are basing your list or search on. If there are multiple fields that you are filtering on and one of them is indexed, choose the indexed field to sort on.

    Construct Lists with Fewer Records
    Try to construct lists that return fewer than 100 records. A list that returns more than a few pages of records is not only cumbersome to navigate, but increases the likelihood of performance issues. Remember that lists are typically meant for access to a user's daily data.

    If you are looking at more than 100 or 200 records, consider using these other features to capture the records instead of lists:

  • Administrative Export
  • Batch Export
  • Reports
  • Web Services (if it is a recurring process)

    Avoid Cross-Object Lists
    A cross-object list is one that filters on a field from another object (record type). For example, you might create a contact list and filter the list on an account field.

    You want to avoid cross-object lists unless that field is an optimized one. So, in our example, a contact list that filters on Account (shown in green in the screenshot) is fine, because Account is optimized.


    However, a contact list that filters on Account Country may cause performance problems.


    Consider Using Other Options

  • Consider using books of business to segment the data for better performance. This allows you to filter on a subset of records when searching or creating lists. Be sure to follow the best practices when creating books.
  • If you are creating lists with more than 4 or 5 criteria, you may want to consider building a report instead. It might help significantly with performance.

    Tips for Administrators

  • Use Custom Indexed Fields Wisely
    Oracle CRM On Demand has a limited number of indexed custom fields available to you. (The number varies according to the field type and object.)


    You want to use those custom indexed fields wisely, making sure to save them for fields that users will filter on when searching records, running lists, or running reports.
    As stated earlier, you may need to migrate data to optimized fields if they are critical to search or sort on.

  • Create Custom Layouts for Search
    The default Search layout for each role includes code to sort on a specific field for each object. As stated previously in these best practices, sorting on different fields can impact performance.

    However, as an administrator, you can copy the default layout (without making any changes) and assign the copied layout to the role. Copying the layout removes the sort that is defined in the default Search layout. Then, when a user filters on a field, the system sorts on the same criteria as the user is searching on.

    If, in addition, you need to add a field to the Search layout, make sure that the field is an optimized one.

  • Comments:

    Fantastic post... I observed this via google.

    Posted by Theron Dsaachs on November 26, 2010 at 06:53 AM PST #

    Post a Comment:
    • HTML Syntax: NOT allowed

    A dialogue about training


    « July 2016