# Improve query performance

Our documentation highlights various query [Performance considerations](/api/predicates/query.md#performance-considerations) that deserve your attention. Start off by spending some time reviewing that section of the docs, and give particular attention to:

- [Inefficient patterns](/api/predicates/query.md#inefficient-patterns) and
- [Efficient patterns](/api/predicates/query.md#efficient-patterns).

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:

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

[Offset](/api/general-concepts.md#pagedqueryresult) 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:

```bash
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 commercetools 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, commercetools 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.

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.

## Related pages

- [Area overview page with navigation](/learning-developer-essentials.md)
- [Previous page: Queries and the SDKs](/learning-developer-essentials/api-queries-query-predicates/queries-and-the-sdks.md)
- [Next page: Learning check](/learning-developer-essentials/api-queries-query-predicates/learning-check.md)