topic Lookup vs join with inputlookup in Splunk Search

Lookup vs join with inputlookup

sboogaar — Tue, 28 May 2019 15:54:00 GMT

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 ]

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?

Re: Lookup vs join with inputlookup

aromanauskas — Sun, 02 Jun 2019 16:45:11 GMT

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.

Re: Lookup vs join with inputlookup

yuanliu — Tue, 25 Feb 2020 19:48:08 GMT

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

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

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.