0

Given the below CTE:

with seekResults as
(
select DISTINCT
 trx_id_fk,
 account_FK, 
 customer_cif,
 customer_tin,
 TIN_Type, 
 ID_NUMBER ,
 SEARCH_NUMBER
 FROM transactionsOP 
) 

 select trx_id_fk 
  ,case when account_FK is not null then (select count(1) from seekResults a where a.account_fk = s.account_fk and a.SEARCH_NUMBER= s.SEARCH_NUMBER) 
   when customer_cif is not null then (select count(1) from seekResults a where a.customer_cif = s.customer_cif and a.SEARCH_NUMBER= s.SEARCH_NUMBER) 
   when customer_tin is not null then (select count(1) from seekResults a where a.customer_tin = s.customer_tin and a.TIN_Type = s.TIN_Type and a.SEARCH_NUM = s.SEARCH_NUM) 
   when ID_NUMBER is not null then (select count(1) from seekResults a where a.ID_NUMBER = s.ID_NUMBER and a.SEARCH_NUMBER= s.SEARCH_NUMBER)
   else (select count(1) from seekResults a where a.SEARCH_NUMBER= s.SEARCH_NUMBER)
       end as 'transaction_period'
from seekResults s  ;

I noticed that it is taking a lot of time doing the calculation because it is calling the seekResults CTE over and over again, mostly in the select count(1) section:

select count(1) from seekResults  a 

Is there a better way to improve that part ? So far I haven't found anything to replace it with.

  • Like in your last question, the only way to avoid this is to persist the CTE results somewhere. You said you couldn't do that because you were limited to working in a view. Have you chosen a different method for calling the data? – Erik Darling Jan 8 '18 at 22:00
  • Hey @sp_BlitzErik, hope you are ok, not possible as there are limitations at backend level. – giantLincecum Jan 8 '18 at 22:02
  • Yep @ypercubeᵀᴹ, added... – giantLincecum Jan 8 '18 at 22:09
  • 1
    Thnx. But there is no account_FK in the select list of the CTE. Please correct. – ypercubeᵀᴹ Jan 8 '18 at 22:19
  • How is the execution time affected if you add a non-deterministic function like rand() in the cte? – Lennart Jan 8 '18 at 22:20
3

I think your query can be written using window aggregates. I don't know if it will be more efficient though:

with seekResults as
(
  select distinct
    trx_id_fk,
    account_fk
    customer_cif,
    customer_tin,
    tin_type,
    id_number,
    search_number
  from transactionsOP 
) 
select 
  trx_id_fk,  
  case 
    when account_fk is not null then
      count(*) over (partition by search_number, account_fk) 
    when customer_cif is not null then
      count(*) over (partition by search_number, customer_cif) 
    when customer_tin is not null then
      count(*) over (partition by search_number, customer_tin, tin_type)
    when id_number is not null then
      count(*) over (partition by search_number, id_number)
    else 
      count(*) over (partition by search_number)
  end as transaction_period
from seekResults ;
  • I don't see how you can mix a single row value (trx_id_fk) with a partition by that can return multiple rows – paparazzo Jan 13 '18 at 15:49
1

You might try to change it to add one more CTE with windows function, and select from there .

with seekResults as
(
select DISTINCT
 trx_id_fk,
 account_FK,
 customer_cif,
 customer_tin,
 ID_NUMBER ,
 SEARCH_NUMBER
 FROM transactionsOP 
) ,
 seekResults2 as 
(
  select  trx_id_fk,
  account_FK,
 customer_cif,
 customer_tin,
 ID_NUMBER ,
 SEARCH_NUMBER,
 count(1) over (partition by account_fk ,SEARCH_NUMBER) as count1,
 count(1) over (partition by customer_cif ,SEARCH_NUMBER) as count2,
 count(1) over (partition by customer_tin , TIN_Type, SEARCH_NUMBER) as count3, 
  count(1) over (partition by ID_NUMBER , SEARCH_NUMBER) as count4
 from seekResults
)
 SELECT  trx_id_fk 
  ,case when account_FK is not null then count1 .... 
 FROM seekResults2
  • Not seeing how this works. The partition by will return multiple rows. – paparazzo Jan 13 '18 at 16:25
0

Just trying to made some sense of this.

Odd a trx_id_fk can have more than one SEARCH_NUMBR but SEARCH_NUMBER is not reported. For that matter trx_id_fk cazn have multiple account_FK and account_FK is not reported.

OP it is not clear what you are trying to do. I am not sure this is what you mean to to.

with seekResults as
( select DISTINCT
         trx_id_fk,
         account_FK, 
         customer_cif,
         customer_tin,
         TIN_Type, 
         ID_NUMBER ,
         SEARCH_NUMBER
  FROM transactionsOP 
) 

select trx_id_fk 
     , case when account_FK   is not null then (select count(1) from seekResults a   
                                                where a.account_fk    = s.account_fk 
                                                  and a.SEARCH_NUMBER = s.SEARCH_NUMBER) 
            when customer_cif is not null then (select count(1) from seekResults a  
                                                where a.customer_cif  = s.customer_cif 
                                                  and a.SEARCH_NUMBER = s.SEARCH_NUMBER) 
            when customer_tin is not null then (select count(1) from seekResults a   
                                                where a.customer_tin  = s.customer_tin and a.TIN_Type = s.TIN_Type 
                                                  and a.SEARCH_NUM    = s.SEARCH_NUM) 
            when ID_NUMBER    is not null then (select count(1) from seekResults a 
                                                where a.ID_NUMBER     = s.ID_NUMBER 
                                                  and a.SEARCH_NUMBER = s.SEARCH_NUMBER)
            else                               (select count(1) from seekResults a 
                                                where a.SEARCH_NUMBER = s.SEARCH_NUMBER)
        end as 'transaction_period'
from seekResults s;

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.