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?
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.
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.