How can we optimize our current search?

[anantdeshpande]

We want to optimize below query as it's taking 4 Min to execute.

index= idx_prod  sourcetype=SRC1 "Sent message:" | rex "Sent message: \w\d+\s+\d(?.\d{6}\s+"] | rex "Sent Mgs:\w\d+\s+\d+\s+\d+{4}(?.[A-Z 0-9]{14})" | search TXN_ID = "$TXN_ID$" 
| join type=outer AIREF_ID [search index= idx_prod  sourcetype= SRC2 "Airef Number" | rex "Airef Number\s+\w\d+(?< AIREF_ID >.\d{6}\s+\s" ] 
| join type=outer AIREF_ID [search index = idx_prod sourcetype =SRC3 " Airef Number " | rex "Airef Number\s+\w\d+(?< AIREF_ID >.\d{6}\s+\s" ] 
| join type=outer AIREF_ID [search index= idx_prod sourcetype = SRC4 “orig mq Content" |rex mq Content\s+\=\s+\w\d+(?< AIREF_ID >.\d{6})ACK” ]
|append [search index= idx_prod  sourcetype=SRC5 "$TXN_ID$" ] 
|append [search index= idx_prod  sourcetype=SRC6 "$TXN_ID$" ]

Query works as below:
1) This query is part of drill-down dashboard. TRAN_ID is passed as token on this dashboard.
2) First query: Extracts AIREF_ID & TRAN_ID. This is the only event where we both ID are present.[search TXN_ID = "$TXN_ID$"] searches for the event where TRAN_ID matches.
3) Join queries: Extracts AIREF_ID and joins with 1st query.
4) Append queries: Searches events for having TXN_ID and appends with 1st query.

How we want to optimize the search:
1) Want to avoid joins.
2) How can we extract & pass AIREF_ID as token in the same dashboard. Something like Run 1st query in the background, extract AIREF_ID & pass it as token in the panel.
3) Does sub-search instead of join will improve performance?

[gcusello]

Hi anantdeshpande,
in your question there is a visualization problem of the regex, you have to format your search as Code.
Every way, you can build your search in this way:

index= idx_prod sourcetype=SRC1 OR  sourcetype=SRC2  OR sourcetype=SRC3 OR  sourcetype=SRC4 OR (sourcetype=SRC5 "$TXN_ID$") OR (sourcetype=SRC6 "$TXN_ID$")
| rex "regex1" | rex "regex2" | rex "regex3" | rex "regex4" 
| search TXN_ID = "$TXN_ID$" 
| ...

Bye.
Giuseppe