Documentation

Improve query performance

Learn how to apply query performance best practices to make your API queries as performant as possible.

  • After completing this page, you should be able to:

    • Identify efficient and inefficient querying patterns in digital commerce scenarios.

    • Our documentation highlights various query Performance considerations that deserve your attention. Start off by spending some time reviewing that section of the docs, and give particular attention to:

    Keep in mind that the queries are ultimately sent to databases for processing, so the more complex the query is, the more time it will take to process and retrieve the information. This is especially true, when the data returned is large.

    We are going to look at a common digital commerce scenario and explore how we can incorporate these performance best practices into our queries.

    Case study: syncing your Orders to an ERP

    Let’s look at a scenario where we want to fetch and sync our Order data to an ERP. The Project in this scenario has a large number of Orders and the Query Predicate used to fetch the Orders was once very fast and over time has slowed down. Our main goal in this situation is to improve the performance of the query by applying our knowledge of inefficient and efficient patterns to rewrite the query.

    For an Order to be synced, it must meet the following conditions:

    • Payment is Paid
    • SyncInfo Channel must be any of the following:
      • 1deeea11-7b41-41c1-bcdb-85e6eb792590,
      • 5cdbd1b7-8f0f-48c6-ae95-37a5b0d2003a,
      • 9b029ff5-1abe-4371-9fb4-3ec65d09a2ba
    • SyncInfo Channel must not be: a96f1ff0-9fa6-4b18-8ff5-6448088ae4e9
    • Custom field forExport is True

    Our current predicate, which we need to improve, is the following:

    paymentState="Paid" and syncInfo(channel(id != "a96f1ff0-9fa6-4b18-8ff5-6448088ae4e9")) and custom(fields(forExport=true))

    Offset is used to paginate the results.

    Task: Before you read further, analyze the above predicate yourself and think about how you could improve it. Then you can check your ideas against those below.

    The improved Query Predicate

    Our suggested improved Query Predicate is the following:

    lastModifiedAt < "{$-24-hours}" AND paymentState="Paid" AND syncInfo(channel(id in ("1deeea11-7b41-41c1-bcdb-85e6eb792590", "5cdbd1b7-8f0f-48c6-ae95-37a5b0d2003a", "9b029ff5-1abe-4371-9fb4-3ec65d09a2ba"))) AND custom(fields(forExport=true))
    

    Query parameters:
    sort: "lastModifiedAt asc"
    limit: 10
    withTotal: false

    Why have we made these suggestions? Let’s look at each change in turn.

    Use the same index to filter and sort

    We are using the same index to filter and sort (lastModifiedAt). This helps Composable Commerce understand that it should use an index based on lastModifiedAt. Sorting on the same index as filtering is more performant than using a separate index.

    Pick one sort type

    For each resource, it’s an efficient pattern to try to pick one sort type and consistently use it for querying that resource. Sorting is also supported by indexes.

    When you consistently query using a sort and the same first where condition, Composable Commerce creates a matching index and this increases the performance of those queries. Reach out to support or your Customer Success Manager to find out if you are already making use of an index, or how many queries are required to build one.

    Use conditions to limit results

    The first where condition used is lastModifiedAt because it will heavily reduce the returned results by the greatest amount compared to other parameters. This remains true when a Project has few or many Orders. Try to limit the where to a date range if possible for resources with many records that will grow over time.

    Avoid negation and use limits

    The Channel part of the predicate is no longer using negation.

    The limit is set to 10 because it is lower than the default (20). Notice that the previous predicate did not pass a limit. You may also increase or decrease the limit based on the actual performance of your predicate and the size of the response.

    Set withTotal to false

    withTotal is set to false, which helps with performance because the system does not need to get the count of all records matching the predicate. We can still determine if we have reached the last page when the results are zero or less than the limit we are using.

    Avoid offset

    If we want to paginate, then we would update the last modified value to the most recent. This is preferred over using offset (the maximum offset is 10, 000).

    Further improvements

    What else could we do to make this simpler? Well, there are still a few further approaches we could try out to make this even more performant. We could try to set a lower PagedQueryResult to decrease the latency. We could also aim to have a response size no greater than 1 MB in general or 5 MB when using Product Projection Search. The response size should be measured by the uncompressed size.

    If we wanted to dig in even further, we could go back to the modeling of the Order and try to improve it. In this case, we should try to understand how syncInfo and forExport map to business requirements and if we can remodel this in another way.

    Please note that queries involving your Orders, Carts, or Customers may slow down over time as your dataset grows. That is why it is important to design your data model effectively and plan how you will use predicates to manage the data.

    Test your knowledge