Skip to main content

Optimising Searches

There are a few ways to make sure that your searches are optimised for performance. This becomes increasingly necessary the more records there are in the system.

  • Make use of the EQUALS_CASE_SENSITIVE operator: We recommend that you do not use the EQUALS operator, as it causes performance issues with larger data sets. Using the EQUALS_CASE_SENSITIVE operator can provide much faster results. The EQUALS operator will transform field values to lowercase before testing against the filter criteria, leading to performance issues.
  • Avoid broad searches: If your searches are returning a lot of results (more than 100k), consider adding narrower filter criteria and avoid pagination with large offset values. If possible, update the filter values instead of paginating by offset. The filter value can be updated based on data form previous searches or by splitting a large interval into smaller equal parts. For example, start by searching for journal entries with creationDate AFTER 2023-07-22T00:00:00, limit=100 and sorted by creationDate in ascending order. Assuming the last journal entry in the result was created on 2023-07-22T01:24:35 continue by searching for journal entries with creationDate AFTER 2023-07-22T01:24:35. Alternatively instead of searching for creationDate ON 2023-07-22 or creationDate BETWEEN 2023-07-22T00:00:00 AND 2023-07-23T00:00:00 and paginating through the results, make more queries with smaller intervals such as creationDate BETWEEN 2023-07-22T00:00:00 AND 2023-07-22T01:00:00..creationDate BETWEEN 2023-07-22T23:00:00 AND 2023-07-23T00:00:00.
  • Use indexed fields for search: For a given entity, certain fields will be indexed in the database. Searching using these fields can dramatically speed up performance. Have at least one highly selective filter based on the indexed columns, do not rely on filter combinations to reduce the number of records returned by the query. If you have access to a database clone, you can use a GUI to list all indexed fields or an SQL query such as: SELECT DISTINCT TABLE_NAME, INDEX_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS.
  • Prefer sorting by a filtered field: Unless the number of records matching the specified filter criteria is very small (~10k) avoid sorting by a different field than the most constraining filter criteria. For example, if you are filtering by lastModifiedDate do not sort by creationDate. When using multiple filter criteria, you should only sort by the field of the most selective filter.
  • Avoid large offsets for pagination: Use a low value for the query offset parameter. Consider adding a supplementary filter or change the value for existing filters to have a lower total number of results rather than have a high offset value. Larger offsets increse response latency because 'skipping records' still requires that the application identifies and sorts the skipped records. For more information, see Pagination.
  • Make use of the limit parameter for single record searches: If you are making a search for something that should only return exactly one result, for example, an account by ID or encoded key, setting a limit of 1 will be more performant than making the same search query with no limit provided. For more information, see Pagination.
  • Do not request full details if they are not required: In most cases, we recommend keeping the default value for the detailsLevel query parameter, which is BASIC and then using the results to make subsequent requests to a GET endpoint using an encoded key or ID. For more information, see Details Level .
  • Time box queries: If you are only interested in results occurring over a given time frame, for example, transactions for a given month or quarter, you can use the BEFORE and AFTER or BETWEEN operators to avoid making searches over the entire database.
  • Do not include null values if they are not needed: If you do not want to search for a field using a null value, do not include null values in the payload of the search request.