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:
Is there a way to avoid that by getting the same resultset and avoid that heavy execution plan ?