Best Practices

The following are some guidelines and recommendations that can help you optimize connector performance.

Design Queries that Use Query Folding

Query folding is a feature that enables the connector to delegate a comparison operation so that it is processed on the server instead of the client. To optimize connector performance, design your queries to filter on columns for which folding is supported. For a list of foldable columns in each table in the schema, see the Simba ServiceNow ODBC Data Connector Reference Guide.

For example, when executing the following query, filtering is delegated to the server:

SELECT * FROM "content_block_detail" WHERE sys_id_value = 6

Whenever possible, use columns that support query folding in your WHERE clauses. If there are any columns in a WHERE clause that cannot be folded, then for those columns the connector must use a less optimal filtering process. This process can consume many API calls and significantly decrease performance. If a WHERE clause contains some columns that support folding and some that do not, then query folding is used where possible and the remaining filtering is processed by the connector instead of the server.

Design Queries That Retrieves a Specific Set of Columns

Most tables in the ServiceNow schema contain a large number of columns. Performing a SELECT * on those tables slows the connector performance as it must parse all the column values for each row returned from the server. Therefore, it is faster to read only a subset of columns using the SELECT clause.

When possible, use WHERE clauses to filter on specific columns, or use the Top N command to limit the number of records that the query applies to.

Use One Session for Multiple Queries

When you run multiple queries, first establish a connection to the instance. Then, reuse this connection for all queries. Otherwise, the connector creates a set of new connection for each query based on the MaxThreads configuration property.

For example, if you use a FOR loop to iterate through multiple queries, establish the connection outside of the FOR loop. Then, perform each query within the FOR loop using the established connection.

Exclude Unused Schemas

Each ServiceNow database table is exposed through two schemas: Actual and Display. For information about these schemas, see Schema Support.

If you need to query data from only one of these schemas, it is recommended that you do one of the following:

In your statement, specify the name of the schema that you want to query.
Or, disable the unused schema by configuring the HideSchemas option. For more information, see HideSchemas.

Excluding an unused schema disables it during the connection. This can shorten BI tool initialization times and also boost connector performance by shortening the body of each response.