---
title: "Best practices for data search (query performance and optimizations)"
canonical: "https://docs.devo.com/space/latest/95206315/Best%20practices%20for%20data%20search%20(query%20performance%20and%20optimizations)"
format: markdown
---
> Macro (toc)
We've collected a number of tips to help you optimize performance and get the most out of what Devo data search offers.
## Optimizing performance
When dealing with large amounts of data, you need to consider the browser's memory restrictions and the processing requirements of different query operations. We have several recommendations to make sure you get the best possible performance:
- [Leveraging the Token Index](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95206315#token.index)
- [WHERE optimizations](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95206315#where.optimizations)
- [GROUP and AGGREGATION optimizations](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95206315#group.aggregation.optimization)
- [Data Structure and Type optimization](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95206315#data.structure.type.optimization)
- [General Query optimization](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95206315#general.query.optimization)
> Macro (anchor)
### Leveraging the Token Index:
- 🟢 **Operators that leverage the Index**: The query engine will leverage the token index when these operators are used. Index usage happens at the beginning of the query for all the tokens at once, and allows to reduce the amount of data (rows) to be considered, while later evaluations of rows will sequentially check each filter.
- **Equality (**`eq`**, **`eqic`, `=`**):** Searches for exact token matches in the index. `eqic` is case-insensitive.
- **Prefix (**`startswith`**):** Searches for prefixes of the constant value in the index.
- **Suffix (**`endswith`**):** Searches for suffixes of the constant value in the index.
- **Substring (**`has`**, **`in`**, **`weakhas`**, **`weakin`**, **`toktains, weaktoktains`**):** Searches for substrings within the field. `toktains` offers fine-grained control over token matching.
- use `toktains` instead of `contains` or `->`.
- `toktains` and `weaktoktains` (case insensitive) have similar performance
- **Contains (**`has`**, **`in`** , **`OR`**):** Checks if a field value is present in a collection of constants. They perform the same because the query engine automatically optimizes them for performance.
- 🟢 **Avoid parser invocation (advanced)**: Use the `raw` column to filter on infrequent terms to bypass parser invocation. Do this as the first `where` of the query. It doesn’t work in all the cases, but worth a try. Disclaimer: when using `toktains` be aware that your logs might be encoded and `toktains` will decode your input (For example, `x="Expaña"` will work differently than `toktains(raw, "España")`, the latter will look for the token `Espa%f1a`)
- Example: `from my.app.log.log where toktains(raw, "specificvalue")`
> Macro (anchor)
### WHERE optimizations
- 🟢 **Move restrictive filters first**: Place filters that significantly reduce the dataset earlier in the query.
- This query:
`from application.log.log `
`where toktains(raw, "INFO")`
`where service="test"`
should be rewritten as follows to achieve better performance:
`from application.log.log `
`where service="test", `
`where toktains(raw, "INFO")`
- 🟢 **Move expensive filters to the end: **regex, lookup, geolocation, percentile, peek are examples of expensive operations.
- 🟢 **Filter Early**: Apply all filters before grouping and making aggregation to minimize the data processed.
- 🟢 **Filter High Cardinality Fields**: To reduce the number of distinct values, apply filters to fields with high cardinality before grouping.
- Be careful if you are grouping by IPs in a table with traffic logs!
- To get the distinct values of a field, you can use the [Stats Count](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95206315#63aa3e18-9d2f-4acb-9b38-67ceb5e049df) or just calculate it yourself with a query like:
`from my.app.log.log `
`group by field1 `
`group `
`select count() as cardinality`
- 🚫 ** **`Not`** Clauses**: `not` operations don’t contribute to leverage the index. Additionally, `not` operations tend to be used in a non-restrictive way. But all depends on the data. If you know that the `not` operation will dramatically reduce the data, then take it up!
- ⚪ **Equality **`eq`**/**`eqic`**/**`=`: Using `eq` (case-sensitive), `eqic` (case-insensitive) and `=` are always good in terms of performance thanks to automatic optimization happening in the query engine.
- 🟢 **Optimize your regex: **Avoid using regex whenever you can, but if you have to use it, then optimize it. This is a computationally expensive operation, move it to the end for better performance. However, if you know that you are dramatically reducing the processed data with your perfect regex, then take it up in the query!
- ⚪ **Membership (**`in`, `or`**)**: The operations `in` and `or` perform in the same way thanks to automatic optimization happening in the query engine. However, using `in` makes your query prettier.
- Example: `x in {a, b, c}` is the same as doing `x = a OR x = b OR x = c`.
> Macro (anchor)
### GROUP and AGGREGATION optimisations:
- 🚫 **Avoid High Cardinality Grouping**: Refrain from grouping by fields with a large number of unique values as it can be resource-intensive. Use the “Server mode” feature in these cases.
- 🟢 **Group by Ranges**: If the field contains numeric values, enrich the data with a new field that identifies a numeric range to which the event belongs, then group by the numeric range instead of the individual values.
- 🚫 **Optimize Heavy Operations**: Be aware of computationally expensive aggregation operations such as `percentileX`, `collectdistinct`, and `peek` with complex regular expressions.
- For `percentileX`, if the column has integer values, consider reducing events through mathematical operations like rounding. Reduce aggregation keys.
- For `peek`, use efficient regular expression patterns to improve performance.
- Always try to narrow down the scope of `jsonparse`, regex, or other operations to specific portions of the log instead of applying them to the entire message.
- 🟢 **Leverage Aggregation Tasks**:
- ACTIVEBOARDS: For recurring queries with grouping used in Activeboard widgets, create aggregation tasks to optimize loading times. Ensure the query feeding the widget includes a grouping and remains the same at least until the grouping instruction. Optimize the query before creating the aggregation task.
- SYNTHETIC DATA: Optimize synthesis tables (views) to ensure efficient queries on the underlying source data.
- 🚫 **Avoid Grouping Long Strings**: Grouping by fields containing long strings can be resource-intensive. If filtering is needed after grouping, try to filter beforehand.
> Macro (anchor)
### Data Structure and Type Optimization:
- 🚫 **Avoid Auto Conversions**: Ensure that when comparing fields with constants, they have the same data type to prevent automatic conversions.
- `eq(string,"123")` is faster than `eq(string,123)` → This is because in the first block we are avoiding the automatic conversion that would be performed in the second block (from integer to string).
- `eq(integer,123)` is faster than `eq(integer,"123")` → This is because in the first block we are avoiding the automatic conversion that would be performed in the second block (from string to integer).
> Macro (anchor)
### General Query Optimization:
- 🟢 **Test with Short Timeframes**: When building queries, limit the time range.
- 🟢 **Disable real-time**: reduce memory usage and speed up testing.
- 🟢 **Switch to server mode: **To prevent browser exhaustion, switching to *server* mode is recommended when dealing with queries that require computationally heavy calculations, especially aggregations. You can also set server mode as the default in user preferences. It will be enabled automatically when using a server operation (lookups, geolocation) after a grouping.
- 🟢 **Manage browser memory: **Restart your browser to free up memory. Minimize the number of open tabs to maximize available memory. In Data Search, keep an eye on the “Execution info” tool.
- 🟢 **Limit concurrent queries: **As a general rule, you should minimize the number of concurrent queries in order to maximize available memory.
- 🟢 **Lookup** **CIDR Matching vs. Individual IP Matching: **Using a shorter list of CIDR (IP ranges) is generally more performant than using a longer list with individual IPs for straight string matching.
## Useful features
There are some great tools available in the search window that you might overlook. Here we list a few that can really come in handy.
- [Execution Info widget in Data Search](#execution.info.widget)
- [Reduce fields](#reduce.fields)
- [Reduce time-range](#reduce.time.range)
- [Send query to the background](#query.background)
- [Understand your data for better optimizations](#understand.data)
- [Use logical operations in the proper order](#logical.operat.order)
> Macro (anchor)
### **Reduce Fields**
Select only necessary fields before opening tables in the Finder
> Macro (anchor)
### **Reduce time-range**
Reduce the time-range before opening tables in the finder and query the most recent data for better performance.
> Macro (anchor)
### Send query to the background
Use the data download when you have already optimized your query and you are ready to increase the time-range considerably.
### > Macro (anchor)
### Understand your data for better optimizations
Use the full stats feature available in Data Search (ask us for it if you don’t see it).
> Macro (anchor)
### Use logical operators in the proper order
Use parentheses to make the order of operations explicit. As an example, these expressions have different results.
```
select
false and false or true and true,
false and (false or true) and true
```
## Other considerations
It’s all about the data. An expensive operation applied over a “cheap to parse” field might change things. Similarly, applying expensive operations over fields that will dramatically reduce the processed data for the latter evaluations.
The query engine will automatically do optimizations that don’t depend on your data. So, the best approach as a user is to focus on the optimizations that depend on the data (by understanding the dataset) and that we can’t do automatically.
Tip: for dataset understanding leverage the “Full stats“ feature in Data Search
## If you are more into videos
This video provides a deep dive into how to write better queries on the Devo platform.
- [The Secret to Faster Devo Queries](https://youtu.be/XLMYEAtFl3U?si=I0GTXpiYYlVBJIS-)