how to improve performances in an appendcols subsearch

[jip31]

hi

I try to improve the performances of the search below

"fo all" is a KV store with 454000 lines

This search takes approximatively 14 secondes

When I have a look to the job inspector, the "appendcols' command takes 11 secondes

Is there a solution to improve the performances of this search??

Thanks

| inputlookup tablet_host.csv 
| lookup lookup_pana "name0" as host OUTPUT CycleCount0 
| where CycleCount0 > 300 
| lookup fo_all HOSTNAME as host output SITE 
| search SITE=$tok_filtersite|s$ 
| stats count as NbHostCycleSup300 
| appendcols
[| inputlookup host.csv 
| lookup fo_all HOSTNAME as host output SITE 
| search SITE=$tok_filtersite|s$ 
| stats count as NbIndHost] 
| eval NbHostCycleInf300 = (NbIndHost - NbHostCycleSup300) 
| eval NbHostCycleSup300=NbHostCycleSup300, NbHostCycleInf300=NbHostCycleInf300
| table NbHostCycleSup300 NbHostCycleInf300 SITE 
| rename NbHostCycleSup300 as "> 300", NbHostCycleInf300 as "< 300" 
| transpose

[Richfez]

Yes, maybe?  Probably, in fact.

But I'm struggling to figure out what you are even doing.  It's like you are smashing several csv files together, sideways, doing some filtering on each then turning the whole thing on its head to make it look like it should have looked in the first place.

Optimizing this sort of thing should start at the beginning.

What is it you are trying to accomplish?  What does the data look like?  What should the output tell you? 

Also, why is there no indexed data involved here at all?  It feels like the better solution would be to index one or both "sets" of this data, and ... well, it'll work better and faster.  But, I'll wait for the answers to the other questions first.  🙂

Happy Splunking,

Rich

[jip31]

thi search is a volume calculation in a pie chart