Regex matches are running very slow

Amritendu Mondal 20 Reputation points Microsoft Employee
2024-03-07T23:24:43.5566667+00:00

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

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
479 questions
0 comments No comments
{count} votes

Accepted answer
  1. BhargavaGunnam-MSFT 25,876 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://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/best-practices

    https://learn.microsoft.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://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/join-time-window

    https://learn.microsoft.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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful