How to optimize my current search for better performance?

[pjampani]

index=*_alltime (sourcetype=*_data earliest=-1d@d latest=@d) 

|table estl_code_enr_stat estl_code_mrkt_offr_typ estl_date_enr_stat_eff estl_nbr_cardh_acct estl_curr_enr_stat_indic estl_rfrn_mrkt_offr_id

|eval Date2=strptime(estl_date_enr_stat_eff , "%m/%d/%Y")
|where estl_code_mrkt_offr_typ="R"

|where (estl_code_enr_stat="E" OR estl_code_enr_stat="P")

|fillnull value="N" estl_curr_enr_stat_indic

|eventstats MAX(Date2) as estl_date_enr_stat_eff_max_R  by estl_code_mrkt_offr_typ estl_nbr_cardh_acct estl_rfrn_mrkt_offr_id

|eval estl_date_enr_stat_eff_max_R =strftime(estl_date_enr_stat_eff_max_R , "%m/%d/%Y")
|rename estl_code_enr_stat as estl_code_enr_stat_R estl_date_enr_stat_eff as estl_date_enr_stat_eff_R estl_code_mrkt_offr_typ as estl_code_mrkt_offr_typ_R estl_curr_enr_stat_indic as estl_curr_enr_stat_indic_R estl_rfrn_mrkt_offr_id as estl_rfrn_mrkt_offr_id_R

|join type=outer estl_nbr_cardh_acct max=0 [search index=***** (sourcetype=***** (estl_code_mrkt_offr_typ=L)  latest=@d)
 |eval Date=strftime(_time,"%m/%d/%Y")
 |fillnull value="N" estl_curr_enr_stat_indic
 |table estl_code_enr_stat estl_code_mrkt_offr_typ estl_date_enr_stat_eff estl_nbr_cardh_acct estl_curr_enr_stat_indic estl_rfrn_mrkt_offr_id _time
 |eval Date=strptime(strftime(_time,"%Y-%m-%d"),"%Y-%m-%d" ) 
 |eval Date1=strptime(estl_date_enr_stat_eff,"%m/%d/%Y")
 |eval zip=mvzip(Date,Date1) |eval zip=mvzip(zip, estl_code_enr_stat)
 |stats max(zip) as zip by estl_code_mrkt_offr_typ  estl_nbr_cardh_acct estl_curr_enr_stat_indic estl_rfrn_mrkt_offr_id
 |eval zip=split(zip, ",") 
 |eval Date=mvindex(zip,0)
 |eval Date1=mvindex(zip,1)
 |eval estl_code_enr_stat=mvindex(zip,2)
 |eval _time=strftime(Date, "%Y-%m-%d") 
 |eval estl_date_enr_stat_eff=strftime(Date1, "%m/%d/%Y")
 |fields - zip Date 
 |where (estl_code_enr_stat="E" OR estl_code_enr_stat="P")

 |eventstats MAX(Date1) as estl_date_enr_stat_eff_max_l by estl_code_mrkt_offr_typ estl_nbr_cardh_acct estl_rfrn_mrkt_offr_id 
 |eval estl_date_enr_stat_eff_max_l =strftime(estl_date_enr_stat_eff_max_l , "%m/%d/%Y") | fields -  Date1 
|rename estl_code_enr_stat as estl_code_enr_stat_L estl_date_enr_stat_eff as estl_date_enr_stat_eff_L Date as Date_L estl_code_mrkt_offr_typ as estl_code_mrkt_offr_typ_L estl_curr_enr_stat_indic as estl_curr_enr_stat_indic_L estl_rfrn_mrkt_offr_id as estl_rfrn_mrkt_offr_id_L ]

| fillnull value=NULL estl_code_enr_stat_L, estl_curr_enr_stat_indic_L,estl_code_mrkt_offr_typ_L,estl_date_enr_stat_eff_L,estl_rfrn_mrkt_offr_id_L
|eval epochyesterday=relative_time(now(),"-1d@d")
|eval yesterday=strftime(epochyesterday,"%m%d%y")
|eval Splunk_Alert_Id="iFIND Pricing and Fees"."||"."LOC POTL R Type"."_".yesterday
|where ( (estl_curr_enr_stat_indic_R="Y" AND (estl_curr_enr_stat_indic_L="Y" OR estl_curr_enr_stat_indic_L="NULL") ))
|where ((estl_code_enr_stat_R!=estl_code_enr_stat_L)  OR (estl_code_enr_stat_R="P" AND estl_code_enr_stat_L="P" AND estl_date_enr_stat_eff_max_R!=estl_date_enr_stat_eff_max_l))

| rename estl_nbr_cardh_acct as nbr_card_acct
|append [|inputlookup LOC_R_TYPE.csv]
| map maxsearches=10000 search="search index=\"*****\" sourcetype=\"******\"  rec_type=\"ACH\" nbr_card_acct=\"$nbr_card_acct$\" | head 1 | table nbr_plastic code_ia_prod_id_demo nbr_card_acct nbr_player_acct1
| eval nbr_card_acct =\"$nbr_card_acct$\" 
| eval Splunk_Alert_Id =\"$Splunk_Alert_Id$\" 
| eval Date_L=\"$Date_L$\"
| eval estl_code_enr_stat_R=\"$estl_code_enr_stat_R$\"
| eval estl_code_mrkt_offr_typ_R=\"$estl_code_mrkt_offr_typ_R$\"
| eval estl_code_mrkt_offr_typ_L=\"$estl_code_mrkt_offr_typ_L$\"
| eval estl_date_enr_stat_eff_L=\"$estl_date_enr_stat_eff_L$\"
| eval estl_date_enr_stat_eff_R=\"$estl_date_enr_stat_eff_R$\"
|table Splunk_Alert_Id nbr_plastic Date_L code_ia_prod_id_demo nbr_card_acct nbr_player_acct1 estl_code_enr_stat_R estl_code_mrkt_offr_typ_R estl_code_mrkt_offr_typ_L estl_date_enr_stat_eff_L estl_date_enr_stat_eff_R"
|where (nbr_player_acct1="100261023")
|where (code_ia_prod_id_demo!="LU7" AND code_ia_prod_id_demo!="LU8" AND code_ia_prod_id_demo!="LU9" AND code_ia_prod_id_demo!="LVA" AND code_ia_prod_id_demo!="LVB" AND code_ia_prod_id_demo!="M8P" AND code_ia_prod_id_demo!="M8Q" AND code_ia_prod_id_demo!="M8R" AND code_ia_prod_id_demo!="M8S"
AND code_ia_prod_id_demo!="M8T" AND code_ia_prod_id_demo!="M8U" AND code_ia_prod_id_demo!="M8V" AND code_ia_prod_id_demo!="M8W" AND code_ia_prod_id_demo!="M8X" AND code_ia_prod_id_demo!="M8Y" AND code_ia_prod_id_demo!="QL5" AND code_ia_prod_id_demo!="QL6"
AND code_ia_prod_id_demo!="QL7" AND code_ia_prod_id_demo!="QL8" AND code_ia_prod_id_demo!="QL9" AND code_ia_prod_id_demo!="QMA" AND code_ia_prod_id_demo!="QMB" AND code_ia_prod_id_demo!="QMC" AND code_ia_prod_id_demo!="QMD" AND code_ia_prod_id_demo!="LV5"
AND code_ia_prod_id_demo!="LVD" AND code_ia_prod_id_demo!="LVK" AND code_ia_prod_id_demo!="LVR" AND code_ia_prod_id_demo!="LVX")

[felipecerda]

Try to move "what you know" to the left side of the query. For example the part where you have where (nbr_player_acct1="100261023"). Move that to line 46 before the table. Filter as early as possible.