Splunk Search

Lookup vs join with inputlookup

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

Builder

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

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
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!