Although it’s often possible–and recommended–to avoid the join command, sometimes it is necessary to use join. I was recently exploring the performance impact of the join command and I wanted to share my findings.

I will demonstrate this situation an unusual query. In order to provide a generic example, I am using the _internal index joined to the _internal index itself. Not a real-world example, but it is sufficient to demonstrate the principal. For this join, I am using the default field _cd, which is not shown in the regular Splunk web search UI, so I copied the field to foo.

Non-optimized search:

index=_internal | eval foo=_cd | join foo [search index=_internal | eval foo=_cd]

Explanation: The join command returns all of the fields back to the browser. For larger queries and events, this can make a substantial difference.

Execution time for 3 invocations (seconds): 5.585, 5.576, 5.302

Better search:

index=_internal | eval foo=_cd | fields foo | join foo [search index=_internal | eval foo=_cd]

Explanation: I’m not sure why, but even in Fast Mode, without the ‘fields foo’ term in the main search, all of the event fields are returned, and search performance suffers.

Execution time for 3 invocations (seconds): 4.879, 4.895, 4.642

Best search:

index=_internal | eval foo=_cd | fields foo | join foo [search index=_internal | eval foo=_cd | fields foo]

Explanation: By returning only the fields we care about, search performance is much better.

Execution time for 3 invocations (seconds): 1.921, 1.949, 1.889

Conclusion: When using /join/, be sure to use /fields/ as well, in both the main search and the subsearch.