We were testing performance and for some reason a join with an inputlookup is faster than a direct lookup.
| 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?
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.
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.