topic Re: Is there any performance impact when you use "search" vs using "where"? in Splunk Search

Is there any performance impact when you use "search" vs using "where"?

ShawnClark — Tue, 01 Sep 2015 21:08:26 GMT

Hi,

I am wondering if there is any guidelines as to using the "search" or "where" commands within a search query when trying to filter data. It seems that both can be used sort of interchangeably. I am curious if there is any performance impact of using one over the other.

Thanks

Re: Is there any performance impact when you use "search" vs using "where"?

MuS — Tue, 01 Sep 2015 21:26:49 GMT

Hi ShawnClark,

there are tons of answers to this question, but here is the best one:

https://answers.splunk.com/answers/128739/difference-between-where-and-search-commands.html

Kudos go to @martin_mueller 😛

cheers, MuS

Re: Is there any performance impact when you use "search" vs using "where"?

ShawnClark — Tue, 01 Sep 2015 21:37:00 GMT

Reading that thread doesn't instill much confidence in the performance difference between using one over the other. The last comment "I doubt there's a significant difference in performance..." is more a gut feeling instead of actually numbers around it. I was hoping to find someone that has done some evaluations of both or a Splunk engineer giving insight on why there is both ways of doing the same thing. If there isn't anything out there then I will have to try some evaluations myself. 😞

Re: Is there any performance impact when you use "search" vs using "where"?

bmacias84 — Tue, 01 Sep 2015 22:12:14 GMT

In my experience the where clause is good for well defined fields/extractions. While the search command allows you to find text which my be in multiple fields or in the _raw data. Depending on the use case one will perform better than the other.

Re: Is there any performance impact when you use "search" vs using "where"?

MuS — Tue, 01 Sep 2015 22:30:46 GMT

Okay, I changed the question since you are interested in the performance impact and not the basic difference between when to use search or where.
Nevertheless I did some basic searches on my VM and here are the results;

Each search was run three times and results are in seconds:

index=_internal earliest=0 | where sourcetype="splunkd" | stats count by sourcetype

38.717
34.757
33.494

index=_internal earliest=0 | search sourcetype="splunkd" | stats count by sourcetype
34.569
36.454
33.638

Hope this helps ...

Re: Is there any performance impact when you use "search" vs using "where"?

martin_mueller — Tue, 01 Sep 2015 23:59:47 GMT

For more speed you'll want this:

 index=_internal earliest=0 sourcetype="splunkd" | stats count by sourcetype

Re: Is there any performance impact when you use "search" vs using "where"?

steveyz — Wed, 02 Sep 2015 00:21:13 GMT

search is going to be slightly more efficient than where, but not enough that you would notice for any realistic search scenario. Both commands can comparing the value of a field to some static value, but that is where the commonality ends. 'where' can be used to compare 2 fields against each other, to compared complex functions of a field to other fields or static values. E.g. you can't do something like to compare fields x and y.

| search x>y

That search would actually search for cases where field x is greater (lexicographically) than the literal value "y"

Note that this is also why the syntax for search and where are slightly different. Because search is designed to compare a field against a static value, it assumes the right hand side of any expression is a literal value. For where, the RHS can be a literal or a field, so literals need to be disambiguated by using double quotes.