Hi, I have a query like below - This query is running very slow. is there anyway I can speedup this query. My table is big and I am ingesting 60 billion rows in one hour. Also I have around 40 regex in the where clause, for simplicity I mentioned only 3. let AllMatches = materialize(Tablename | where TIMESTAMP >= ago(10m) and TIMESTAMP < now() | where (Message matches regex regex1) or (Message matches regex regex2) or (Message matches regex regex3)); AllMatches | project TIMESTAMP, Message

Hello Amritendu Mondal , Welcome to the Microsoft Q&A forum. As your query is filtering the data based on a set of regular expressions, which can be a resource-intensive operation. Below are the few suggestions to speed up the query: Reduce the amount of data being processed by filtering the data as early as possible in the query, using a datetime column to help Kusto optimize its query operation Avoid using redundant qualified references Use the materialize() function: This function can be used to speed up queries that perform heavy calculations whose results are used multiple times in the query. It evaluates a tabular expression only once. Consider union operator for the filtering, parsing and summarize operations. Use the has_any operator instead of multiple or conditions Reference document: https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/best-practices https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/materialize-function https://stackoverflow.com/questions/61261558/is-it-possible-for-better-optimization-of-my-kusto-query https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/join-time-window https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/parse-operator https://stackoverflow.com/questions/65310383/how-to-use-regex-in-kusto-query I hope this helps.

Regex matches are running very slow

Accepted answer

BhargavaGunnam-MSFT 28,526 Reputation points Microsoft Employee

2024-03-08T21:21:37.3166667+00:00
Hello Amritendu Mondal,

Welcome to the Microsoft Q&A forum.

As your query is filtering the data based on a set of regular expressions, which can be a resource-intensive operation.

Below are the few suggestions to speed up the query:

Reduce the amount of data being processed by filtering the data as early as possible in the query, using a datetime column to help Kusto optimize its query operation

Avoid using redundant qualified references

Use the materialize() function: This function can be used to speed up queries that perform heavy calculations whose results are used multiple times in the query. It evaluates a tabular expression only once.

Consider union operator for the filtering, parsing and summarize operations.

Use the has_any operator instead of multiple or conditions

Reference document:

https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/best-practices

https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/materialize-function

https://stackoverflow.com/questions/61261558/is-it-possible-for-better-optimization-of-my-kusto-query

https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/join-time-window

https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/parse-operator https://stackoverflow.com/questions/65310383/how-to-use-regex-in-kusto-query

I hope this helps.
Please sign in to rate this answer.

1 person found this answer helpful.
Amritendu Mondal 20 Reputation points Microsoft Employee

2024-03-09T10:03:23.5033333+00:00

okay. non of the suggested method is applicable for my case I think. So can I use materialized view somehow and precompute the regex match for each row and use it to filter later on. For example in materialized view can I save the result of regex match with message column for each row ?

Amritendu Mondal 20 Reputation points Microsoft Employee

2024-03-10T05:49:21.8466667+00:00

okay. none of the suggested method is applicable for my case I think. So can I use materialized view somehow and precompute the regex match for each row and use it to filter later on. For example in materialized view can I save the result of regex match with message column for each row ?

BhargavaGunnam-MSFT 28,526 Reputation points Microsoft Employee

2024-03-11T21:28:32.86+00:00

Yes, my understanding is you can use a materialized view to precompute the regular expression matches for each row and use it to filter later on

Syntax

.create [async] [ifnotexists] materialized-view [ with (PropertyName = PropertyValue,...)] MaterializedViewName on table SourceTableName { Query }

and then query and filter the materializedview

PrecomputedMatches

| where RegexMatch1 or RegexMatch2 or RegexMatch3

| project TIMESTAMP, Message

https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/management/materialized-views/materialized-view-create

Amritendu Mondal 20 Reputation points Microsoft Employee

2024-03-12T05:49:45.4533333+00:00

Hi,

I was thinking if that can be faster, however it seems materialized view needs to be with a summarize operator. Which summarize operator I should use anyway; I don't need a summarize operator but filter regex?

Thank you.

BhargavaGunnam-MSFT 28,526 Reputation points Microsoft Employee

2024-03-12T19:09:11.2266667+00:00

Yes, to use Materialized view- The query should reference a single fact table that is the source of the materialized view. It should include a single summarize operator, and one or more aggregation functions aggregated by one or more groups by expressions. The summarize operator must always be the last operator in the query.

With out summarization, materialized views doesn't support.

You need to explore other options like: partitioning strategies, or creating intermediate tables(stores the rows from the original table for regex matches)

BhargavaGunnam-MSFT 28,526 Reputation points Microsoft Employee

2024-03-18T21:17:06.92+00:00

Hello Amritendu Mondal,

I am checking to see if you have any further questions here.

Amritendu Mondal 20 Reputation points Microsoft Employee

2024-03-18T22:40:05.2633333+00:00

Okay sure. Can you elaborate on partition strategies, what are my options for partitioning and what should I explore?

BhargavaGunnam-MSFT 28,526 Reputation points Microsoft Employee

2024-03-20T23:53:47.32+00:00

When choosing a partitioning strategy, you should consider the data volume, query patterns, data distribution and Data cardinality.

Here is a very well written blog post on Data partitioning in Kusto

https://y0nil.github.io/kusto.blog/blog-posts/data-partitioning.html

https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/management/partitioning-policy#the-data-partitioning-process

I hope this helps.
Sign in to comment

Share via

Regex matches are running very slow

0 additional answers