I need your help, I need some guiadance to improve the performance for the following given view.

I have a view writen with the below code:

with timeframes  as ( select p.SEARCH_NUM,  case   when p.FROM_DATE is not null then p.FROM_DATE        when p.FROM_DATE is null and P.SEARCH_DAYS is not null and p.TO_DATE is not null then DATEADD(day,p.SEARCH_DAYS*-1,p.TO_DATE)        when p.FROM_DATE is null and P.SEARCH_DAYS is not null and p.TO_DATE is null then DATEADD(day,p.SEARCH_DAYS*-1,GetDate())         when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is not null and p.DURATION = 'Yearly' then DATEADD(year,-1,p.TO_DATE)        when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is null and p.DURATION = 'Yearly' then DATEADD(year,-1,GetDate())        when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is not null and p.DURATION = 'Monthly' then DATEADD(month,-1,p.TO_DATE)        when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is null and p.DURATION = 'Monthly' then DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)        when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is not null and p.DURATION = 'Weekly' then DATEADD(day,-7,p.TO_DATE)        when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is null and p.DURATION = 'Weekly' then DATEADD(day,-7,GetDate())        else DATEADD(month,-1,GetDate())   end as FROM_DATE   ,case when p.TO_DATE is not null then p.TO_DATE     when p.TO_DATE is null and p.DURATION = 'Monthly' then DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)        else GetDate()   end as TO_DATE from dbo.parmeters_table as p ) , transactions as (   select tm.SEARCH_NUM, tr.id from dbo.ixf_transaction tr   inner join timeframes tm on tr.transaction_date between tm.FROM_DATE and tm.TO_DATE ) , searchResults AS ( select DISTINCT   t.SEARCH_NUM   ,trx.id as 'trx_id_FK'   ,trx.institution_FK   ,trx.branch_FK   ,branch.code as 'branch_number'   ,trx.account_FK   ,trx.transaction_date as 'trx_date'   ,case when trx.type_of_transaction = 'I' or trx.type_of_transaction = 'B'     then trx.base_currency_amount else 0 end as 'cash_in'   ,case when trx.type_of_transaction = 'O' or trx.type_of_transaction = 'B'     then trx.base_currency_amount else 0 end as 'cash_out'     ,case when trx.type_of_transaction = 'B'     then trx.base_currency_amount else 0 end as 'curr_exchange'     ,trx.base_currency_amount       ,trx.type_of_transaction   ,trx.teller_id   ,trx.unique_trans_id   ,trx.serial_number   ,trx.foreign_amount   ,trx.country_of_currency_FK   ,trx.flex_1   ,trx.flex_2   ,trx.customer_FK   ,ttr.code as 'trx_code'   ,ttr.description as 'trx_description'   ,ttr.irs_transaction_id  ,cust.full_name   ,str(cust.web_reference_id) as 'web_reference_id'   ,conben.customer_cif   ,conben.id_number   ,conben.id_type  ,conben.other_description as 'id_type_other_description'  ,conben.customer_tin   ,conben.cust_type_fk as 'TIN_Type'  ,ctrtx.is_teller from ixf_transaction trx   inner join transactions t on trx.id = t.id   inner join type_ref ttr on ttr.id=trx.transaction_type_FK   inner join unit branch on branch.id=trx.branch_FK and branch.object_type='Branch'   left join  cust on cust.id = trx.customer_FK   left join  cashtx_cus conben on conben.transaction_id = trx.unique_trans_id    and conben.customer_FK = trx.customer_FK   left join  trans ctrtx on ctrtx.cash_transaction_fk = trx.id )     select trx_id_fk    ,case when account_FK is not null then (select count(1) from searchresults a where a.account_fk = searchresults.account_fk and a.SEARCH_NUM = searchresults.SEARCH_NUM)     when customer_cif is not null then (select count(1) from searchresults a where a.customer_cif = searchresults.customer_cif and a.SEARCH_NUM = searchresults.SEARCH_NUM)     when customer_tin is not null then (select count(1) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.SEARCH_NUM = searchresults.SEARCH_NUM)     when ID_NUMBER is not null then (select count(1) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.SEARCH_NUM = searchresults.SEARCH_NUM)    else (select count(1) from searchresults a where a.SEARCH_NUM = searchresults.SEARCH_NUM)    end as 'trx_per_period'   ,case when account_FK is not null then (select count(1) from searchresults a where a.account_FK = searchresults.account_FK and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)     when customer_cif is not null then (select count(1) from searchresults a where a.customer_cif = searchresults.customer_cif and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)     when customer_tin is not null then (select count(1) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when ID_NUMBER is not null then (select count(1) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    else (select count(1) from searchresults a where a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    end as 'trx_per_day'   ,case when account_FK is not null then (select sum(cash_in) from searchresults a where a.account_FK = searchresults.account_FK and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when customer_cif is not null then (select sum(cash_in) from searchresults a where a.customer_cif = searchresults.customer_cif and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when customer_tin is not null then (select sum(cash_in) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when ID_NUMBER is not null then (select sum(cash_in) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    else (select sum(cash_in) from searchresults a where a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    end as 'total_per_day_cash_in'   ,case when account_FK is not null then (select sum(cash_out) from searchresults a where a.account_FK = searchresults.account_FK and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when customer_cif is not null then (select sum(cash_out) from searchresults a where a.customer_cif = searchresults.customer_cif and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when customer_tin is not null then (select sum(cash_out) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when ID_NUMBER is not null then (select sum(cash_out) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    else (select sum(cash_out) from searchresults a where a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)    end as 'total_per_day_cash_out'   ,case when account_FK is not null then (select sum(cash_in) from searchresults a where a.account_FK = searchresults.account_FK and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when customer_cif is not null then (select sum(cash_in) from searchresults a where a.customer_cif = searchresults.customer_cif and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when customer_tin is not null then (select sum(cash_in) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when ID_NUMBER is not null then (select sum(cash_in) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.SEARCH_NUM = searchresults.SEARCH_NUM)    else (select sum(cash_in) from searchresults a where a.SEARCH_NUM = searchresults.SEARCH_NUM)    end as 'total_per_period_cash_in'   ,case when account_FK is not null then (select sum(cash_out) from searchresults a where a.account_FK = searchresults.account_FK and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when customer_cif is not null then (select sum(cash_out) from searchresults a where a.customer_cif = searchresults.customer_cif and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when customer_tin is not null then (select sum(cash_out) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.SEARCH_NUM = searchresults.SEARCH_NUM)    when ID_NUMBER is not null then (select sum(cash_out) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.SEARCH_NUM = searchresults.SEARCH_NUM)    else (select sum(cash_out) from searchresults a where a.SEARCH_NUM = searchresults.SEARCH_NUM)    end as 'total_per_period_cash_out'    from SearchResults

The heavy part is the search result cte which is calling itself over and over, And the execution plan looks as per below:

enter image description here

Is there a way to avoid that by getting the same resultset and avoid that heavy execution plan ?

✓ Extra quality

ExtraProxies brings the best proxy quality for you with our private and reliable proxies

✓ Extra anonymity

Top level of anonymity and 100% safe proxies – this is what you get with every proxy package

✓ Extra speed

1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!

50 proxies

$19/month

50% DISCOUNT!

$0.38 per proxy

✓ Private

✓ Elite

✓ Anonymous

Buy now

100 proxies

$29/month

50% DISCOUNT!

$0.29 per proxy

✓ Private

✓ Elite

✓ Anonymous

Buy now

200 proxies

$49/month

50% DISCOUNT!

$0.25 per proxy

✓ Private

✓ Elite

✓ Anonymous

Buy now

500 proxies

$109/month

50% DISCOUNT!

$0.22 per proxy

✓ Private

✓ Elite

✓ Anonymous

Buy now

1,000 proxies

$179/month

50% DISCOUNT!

$0.18 per proxy

✓ Private

✓ Elite

✓ Anonymous

Buy now

2,000 proxies

$299/month

50% DISCOUNT!

$0.15 per proxy

✓ Private

✓ Elite

✓ Anonymous

Buy now

USA proxy location

We offer premium quality USA private proxies – the most essential proxies you can ever want from USA

100% anonymous

Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies

Unlimited bandwidth

Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!

Superfast speed

Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!

99,9% servers uptime

Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems

No usage restrictions

You have freedom to use your proxies with every software, browser or website you want without restrictions

Perfect for SEO

We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies

Big discounts

Buy more proxies and get better price – we offer various proxy packages with great deals and discounts

Premium support

We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!

✓ Extra quality

✓ Extra anonymity

✓ Extra speed

50 proxies

$19/month

100 proxies

$29/month

200 proxies

$49/month

500 proxies

$109/month

1,000 proxies

$179/month

2,000 proxies

$299/month

USA proxy location

100% anonymous

Unlimited bandwidth

Superfast speed

99,9% servers uptime

No usage restrictions

Perfect for SEO

Big discounts

Premium support

Satisfaction guarantee

Best Proxy Packs

Quick Links

Like And Follow Us