1

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 ?

  • 4
    Is the DISTINCT needed? Whenever I see DISTINCT or GROUP BY after a "heavy" FROM ("heavy as a join of 8 tables), I wonder if the SQL developers knew what they were doing. – ypercubeᵀᴹ Dec 31 '17 at 17:15
7

I think you might be writing code under a faulty assumption: CTE results are persisted

They're not, and every time you reference them, the syntax is re-executed.

Here's a quick example:

CREATE TABLE #dummy
(
    id INT
);
INSERT #dummy ( id )
VALUES ( 1 );


WITH yourmom
AS ( SELECT d.id
     FROM   #dummy AS d )
SELECT ym.id
FROM   yourmom AS ym
JOIN   yourmom AS ym2
ON ym2.id = ym.id
JOIN   yourmom AS ym3
ON ym3.id = ym.id;

If you look at the query plan, there are three scans of the base table -- once for the initial FROM and once for each JOIN. That's three in total.

This is causing issues in a few places:

  1. Your join in the transactions CTE
  2. Your to transactions in searchResults
  3. All those COUNT subqueries in your final select from searchresults

That leads to your second problem: those two CASE expressions you use to figure out your FROM and TO dates are totally non-SARGable.

You have two options:

  1. Stick the result of your first CTE into a #temp table

  2. Add computed columns to your base table

I'd probably opt for the computed columns in this case

ALTER TABLE dbo.parmeters_table
ADD FROM_DATE_SEARCHED AS CASE WHEN FROM_DATE IS NOT NULL THEN FROM_DATE
                               WHEN FROM_DATE IS NULL
                                    AND SEARCH_DAYS IS NOT NULL
                                    AND TO_DATE IS NOT NULL THEN DATEADD(DAY, SEARCH_DAYS * -1, TO_DATE)
                               WHEN FROM_DATE IS NULL
                                    AND SEARCH_DAYS IS NOT NULL
                                    AND TO_DATE IS NULL THEN DATEADD(DAY, SEARCH_DAYS * -1, GETDATE())
                               WHEN FROM_DATE IS NULL
                                    AND SEARCH_DAYS IS NULL
                                    AND TO_DATE IS NOT NULL
                                    AND DURATION = 'Yearly' THEN DATEADD(YEAR, -1, TO_DATE)
                               WHEN FROM_DATE IS NULL
                                    AND SEARCH_DAYS IS NULL
                                    AND TO_DATE IS NULL
                                    AND DURATION = 'Yearly' THEN DATEADD(YEAR, -1, GETDATE())
                               WHEN FROM_DATE IS NULL
                                    AND SEARCH_DAYS IS NULL
                                    AND TO_DATE IS NOT NULL
                                    AND DURATION = 'Monthly' THEN DATEADD(MONTH, -1, TO_DATE)
                               WHEN FROM_DATE IS NULL
                                    AND SEARCH_DAYS IS NULL
                                    AND TO_DATE IS NULL
                                    AND DURATION = 'Monthly' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
                               WHEN FROM_DATE IS NULL
                                    AND SEARCH_DAYS IS NULL
                                    AND TO_DATE IS NOT NULL
                                    AND DURATION = 'Weekly' THEN DATEADD(DAY, -7, TO_DATE)
                               WHEN FROM_DATE IS NULL
                                    AND SEARCH_DAYS IS NULL
                                    AND TO_DATE IS NULL
                                    AND DURATION = 'Weekly' THEN DATEADD(DAY, -7, GETDATE())
                               ELSE DATEADD(MONTH, -1, GETDATE())
                          END,
    TO_DATE_SEARCHED AS CASE WHEN TO_DATE IS NOT NULL THEN TO_DATE
                             WHEN TO_DATE IS NULL
                                  AND DURATION = 'Monthly' THEN DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1)
                             ELSE GETDATE()
                        END;

You can index these columns to make your join to dbo.ixf_transaction more efficient and avoid having to pre-process all the data and then join to it.

And I'd lean towards a temp table for persisting the results of searchResults. This prevents the need for re-execution for each CASE expression.

Hope this helps!

  • 1.- Stick the result of your first CTE into a #temp table A= temp tables are not allowed within a view. 2.- Add computed columns to your base table A= in this case this option did not help as the heavy part still being the SearchResult CTE :/, the query plan is the same. – giantLincecum Dec 31 '17 at 17:02
  • @CarlosAlejandrodelosReyes You may need to use something other than a view to realize the performance gains you seek, then. I'm happy to delete my answer if you'd like. – Erik Darling Dec 31 '17 at 17:04
  • Your answer is very valuable, I'll keep looking for the best approach on this and share my findings :). – giantLincecum Dec 31 '17 at 17:05
  • @CarlosAlejandrodelosReyes just adding the computed columns is half the battle -- you'd need to index them in order for them to make any difference. That won't help with the problems in searchResults, though, and it can't be presented as an indexed view with the LEFT JOINs involved. Perhaps you can edit your question to explain why you need to do this in a view rather than a stored procedure? – Erik Darling Dec 31 '17 at 17:10
  • You can still persist the cte in memory in a view, you just cannot use persisted temporary tables. Nothing stops you from sticking the results in a table-valued variable that will reside in memory for the scope of the transaction. DECLARE @cte TABLE (col1 INT, col2 INT); INSERT INTO @cte (col1, col2) SELECT val1, val2 FROM ...; – Drunken Code Monkey Jan 10 '18 at 4:18
4

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

Try to persist the result from cte searchresult to a temp table and use the temp table in the main query instead

  • But temp tables cannot be used within the view :/. – giantLincecum Dec 31 '17 at 16:47
  • Yeah, I missed that this was part of a view. – Mikael Eriksson Dec 31 '17 at 16:54
  • You cannot use persistent temporary tables (# prefix), but you can still use table-valued variables. Just DECLARE @cte TABLE (col1 INT, col2 INT); INSERT INTO @cte (col1, col2) SELECT val1, val2 FROM ...; – Drunken Code Monkey Jan 1 '18 at 1:15
1

In a nutshell it looks like you're trying to do too much all in one go :o)

That last big SELECT at the bottom with all the parallel CASE expressions is part of the problem. You should make everything below that one view, then break that big query at the end into either one JOIN against it (with those conditions in the ON clause) or possibly even five separate queries to match each of those conditions. Either way you really, really want to get rid of those sub-selects in cases (especially since they appear to do a SUM of the same column in every branch of the CASE unless I missed something).

Edit: OK, I spoke too soon, you do seem to have different conditions in some of the sub-selects, but even so, you could reduce that to two JOINS, or again maybe just two separate queries, one for the data which needs the a.trx_date = searchresults.trx_date condition and one for the data which doesn't. You probably understand the data better at this point, so I'm not going to keep on wading out of my depth.

I'd probably turn a couple of the CASE branches in your timeframes CTE into COALESCE but that's just a matter of style and/or taste.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.