Splunk Search

Lookup vs join with inputlookup

sboogaar
Path Finder

We were testing performance and for some reason a join with an inputlookup is faster than a direct lookup.
Sample query:

| dedup serviceid
| rename serviceid as service_id 
| join type=outer service_id 
[| inputlookup service_runtime ] 

VS

index=itsi_summary 
| dedup serviceid
| rename serviceid as service_id 
| lookup service_runtime service_id

I thought the lookup would be faster and basicly execute the join with the inputlookup itself. But after trying a few hundred times 99% of the time the join with inputlookup is faster.
In what cases should we use lookup instead of a join with an inputlookup?

0 Karma

yuanliu
SplunkTrust
SplunkTrust

I got the exact opposite observation, hence the same question.

index=myindex
| join field1
    [inputlookup table1]
| more filters

vs

index=myindex
| lookup table1 field1 as field1 OUTPUTNEW field2 as field2
| where field2 == 'value2'
| more filters

The first search (join) nearly quadruples the time used by the second (lookup). More interestingly, join itself only consumes a fraction of the extra time. (My lookup table is only a few lines.)

To make matter even more interesting, this search (without explicit join)

index=myindex [ | inputlookup table1 |fields field1 ]
| more filters

is about as fast or marginally faster than the second (lookup). All three are functionally identical for my purpose.

0 Karma

aromanauskas
Path Finder

Here you are doing a very unique operation of comparing two tables and choosing to combine them by a field, and combining ALL fields in the table. This is only faster because you have one field to compare and have already run a dedup on the tables. If at any point you required multiple matches or have multiple fields to match on or the search retrieves only a small number of events and the lookup table is tens of thousands or more the lookup table method would be the appropriate option.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...