how to avoid to use join

[RiccardoV]

Hi,
I'm using Splunk 6.1 and I have two sourcetype for my data:
the first contains a list of events of this type

id category

the second contains events from a forwarder, and they have those fields:

id field1 field2 etc

I have to join those sourcetype, I want to draw some graphs counting how much id on the second sourcetype belong to the various categories.

Right now I have this query:

sourcetype="source2"
| table Id
| join type=inner Id [search sourcetype="source1" | table Id, category]
| table Id, category

How can I avoid to use join to improve performance?
Please note that source1 contains 12 millions of row, and it is updated daily (more or less), on the contrary source1 is updated constantly.

[somesoni2 · Accepted solution]

One suggestion for query using join itself for faster result.(assuming you want to plot count of ids from source2 based on category from source1)

sourcetype="source2" | stats count by Id | join Id [search sourcetype="source1" | table Id, category] |...other calculations...

Option without join

sourcetype="source2" OR sourcetype="source1" | table Id , category| stats count(eval(isnull(category))) as count values(category) as category by Id