This post discusses how to best leverage file field attributes in queries on Knowledge Articles for optimal performance.  Note that one could implement basic search/retrieve operations using the search REST API provided by Salesforce, however, this post assumes a use case where additional logic needs to be implemented in Apex.

One such case is to include a File field attribute on the Article Type which allows a file to be uploaded to articles of that type. These files become accessible on the base Article Type’s <ArticleTypeName>__kav object and can be queried via SOQL and SOSL.  While conveniently accessible, we have seen cases where these attributes have been included unnecessarily in the query causing unexpected increases in query times, leading to a sub-optimal experience for end users.

To illustrate, consider the use case that you want to build an online Help-based WebUI on Heroku, providing real-time search and retrieval of content from a Salesforce Knowledge base with custom UI and sorting logic.

For this hypothetical case, you would create a Help Article Type, add a Rich Text field “Body” for the content, add a File attribute “File Attachment” for supplemental attachments on the article, and setup a “Product Release” data category.  Lastly, you would build out a REST-based interface via Apex and a web UI, any flavor, with which to integrate.

For this post, the focus will be on the Force.com portion of the implementation. To keep things simple, we’ll assume this Help interface limits support to basic search and retrieve operations, to which Apex methods are built accordingly.

First, let’s focus on the search operation query, where the goal is to find articles matching the search criteria. The Apex SOSL query code would look something like this:

This query provides the list of matching Article IDs in the search_results[0] index. This list can then be serialized as JSON and passed back to the calling client, or used in additional queries as needed.

Second, we consider article retrieval, where the intent is to fetch one or more of the Articles and display their file attachments to the end user.  The Apex query to perform this task can take several forms, however, it might be tempting to take this approach:

Now, this query has a potential weakness.  Without going into too much detail, know that the Knowledge Article Type attributes and the file field data are stored separately in the Force.com platform.  This means that a single SOQL query that includes any file field “__Body__s” attributes will effectively cause the platform to read into memory the Article attributes and the file field data at runtime. This can lead to longer response times which can degrade the end user experience.

While the example given above is suitable for retrieving a single Article’s details and associated file field data for display or download to the client, there are several reasons why this approach is suboptimal:

1. If the input list size became large the amount of file field data to be read and sent back to the client could become excessive, leading to timeouts or errors.

2. Browsers/clients could achieve greater throughput via parallel requests for the file data.

3. It is not REST-ful behavior to include file field data in the JSON response payload.

So, instead of including the <File Field Attribute>__Body__s attribute(s), we recommend that the query contain only the attributes needed to provide a summary response and provide a link to the file field(s) which can then be downloaded via separate HTTP requests.  Additionally, if the number of files to retrieve is large, and mass export is needed, the recommendation is to use Data Export.

Concretely, the above retrieval query should be re-written as follows:

Then, provide a link to the File attachment, which is done by concatenating together a relative-path URL to the fileField servlet with the following pattern:

Such as:

In summary, queries including file field attributes can result in longer response times and a suboptimal user experience. Applying this best practice will ensure that queries on Articles will only fetch the minimal data needed from the database. Then, using the article header attributes to provide direct links to the file data, will result in shorter query times and faster response times.  This allows for client request parallelism, providing REST-ful behavior and, ultimately, a better end user experience.

Related Links:

For additional information, see the following resources:

Custom Article Types
Exporting Backup Data
Knowledge Developer’s Guide
Data Category Management
SOSL/SOQL Queries with Data Category Criteria
Force.com REST API

About the Author and CCE Technical Enablement