Is the search job inspector option the best way to determine the performance of a search query?

[vpurushottam]

Hi,

I am pretty new to Splunk and wanted to know how to determine the performance of a query? Is it through the "Inspect Job" option?

And also, can anyone help me with optimizing the following query or is it already optimized?:

index = "example" 
|lookup Lookup_1 RecordNumber OUTPUT Location, VIP_Guest_Number
|search Location != ""
|eval Guest_Name=upper(Guest_LN) + ", "+upper(Guest_FN) 
|lookup Lookup_2 "User ID" as UID OUTPUT "Department Name" as Department, "Institution" as Institution, Title, Manager as MUID 
|table Arrival_Date, UID, User_Name, RecordNumber, Guest_Name, Location, VIP_Guest_Number, Metric_Name, Department, Institution, Title, MUID 

This query takes about 220 seconds (as seen on the Inspect Job tab) to generate results when given around 12-13 million rows.

Is there any way I can make it work faster or is it what it is?

Thank you in advance

[gstultz_splunk]

I can think of a few things that may speed up the search...in no particular order try these.
1. Limit the initial time range for the "index="example". You can eliminate a lot of buckets by specifying the time.
2. Do you need all of the fields returned by the initial "index = "example" portion? If not, use the "fields" command to reduce the amount of data that is manipulated.

3. Can you be more specific in your initial search? If you can add more matching key/value pairs to reduce the data so much the better
4. Use the "Fast" mode to search, not Smart or Verbose modes.
5. Replace the ... | search Location != "" line with ... | where isnotnull(Location). != forces all of the data to be searched first, then return the events that don't match.
6. Move the eval statement after the second lookup.

I'd be curious to hear if there's any time improvements.

[chrisyounger · Accepted solution]

This is an excellent video about the job inspector: https://www.youtube.com/watch?v=n3OqaB6GVXs

Docs article explaining what the job inspector fields mean: https://docs.splunk.com/Documentation/Splunk/7.2.3/Search/ViewsearchjobpropertieswiththeJobInspector

Good article about writing better searches: https://docs.splunk.com/Documentation/Splunk/7.2.3/Search/Writebettersearches

Your query already looks really good to me. There is nothing obvious I can see for making it faster. Lookups are typically very quick. All your commands you used are distributable to the indexers so they benefit well from parallelisation.