Modify/tuning SQL statements to improve performance in execute
Hi All,
I`ll appreciate modifying code to improve it`s performance.
thanks in regard,
there is the code:
I`ll appreciate modifying code to improve it`s performance.
thanks in regard,
there is the code:
with KT
(Document_Id, Handle,AccountBranchId, AccountNumber , TaskName,ActionID, CustomerIdentity, CustomerSeq,CustomerFirstName, CustomerLastName, CustomerPhone )
as
(select distinct
concat(' KT-',cast(Document_Control_ID as varchar(50))) Document_Id,
concat(' ',cast(cast(open_date as date format 'DD-MM-YYYY') as char(10)),' KT-',cast(Document_Control_ID as varchar(49))) as Handle,
substring(a.account_id,4,3) as AccountBranchId,
concat('0000',substring(a.account_id,8,6)) as AccountNumber,
case
when account_type_code = 3 then
when Account_Attribute_Code = 826 then
when partycount = citizenshipIlCount and partycount = residencyIlCount and Account_Attribute_Code = 61 then
when (Acct_Ownership_Type_Code in (0,1) or account_type_code = 3) and h.residence_country_code = 602 then
when (account_type_code=2 or (account_type_code=5 and account_sub_type_code in (2,3,13,14,15,16))) and h.residence_country_code = 602 and customer_id_document_type_code= 4 and i.affidavit_type_code = 2 then
when (Acct_Ownership_Type_Code in (0,1) or account_type_code = 3) and residencyIlCount = 0 and ResidencyUSCount = 0 then
when (account_type_code=2 or (account_type_code=5 and account_sub_type_code in (2,3,13,14,15,16))) and customer_id_document_type_code= 4 and i.affidavit_type_code = 2 and residence_country_code not in (212,602) then
when E.Acct_Residence_Code = 12 then
when Business_Ind= and customer_country_code = 602 and residence_country_code = 212 then
when Business_Ind= and customer_country_code = 212 and residence_country_code = 602 then
when Business_Ind= and customer_country_code = 602 and residence_country_code = 602 then
when Business_Ind= and
(
(customer_country_code not in (212,602) and residence_country_code = 212)
or (residence_country_code not in (212,602) and customer_country_code = 212)
or (residence_country_code not in (212,602) and customer_country_code not in (212,602))
)
then
when Acct_Ownership_Type_Code in (0,1) then
when Affidavit_Sub_Type_Code in (2,3) and g.Affidavit_Type_Code = 2 and (e.account_type_code=2 or (e.account_type_code=5 and account_sub_type_code in (2,3,16))) then
when Affidavit_Sub_Type_Code not in (2,3) and g.Affidavit_Type_Code = 2 and (e.account_type_code=2 or (e.account_type_code=5 and account_sub_type_code in (2,3,16))) then
when account_type_code=5 and account_sub_type_code in (14,15) then
when account_type_code=5 and account_sub_type_code=13 then
when Business_Ind= then
else
end as TaskName,
case
when account_type_code = 3 then 7502
when Account_Attribute_Code = 826 then 7503
when partycount = citizenshipIlCount and partycount = residencyIlCount and Account_Attribute_Code = 61 then 7507
when (Acct_Ownership_Type_Code in (0,1) or account_type_code = 3) and h.residence_country_code = 602 then 7509
when (account_type_code=2 or (account_type_code=5 and account_sub_type_code in (2,3,13,14,15,16))) and h.residence_country_code = 602 and customer_id_document_type_code= 4 and i.affidavit_type_code = 2 then 7510
when (Acct_Ownership_Type_Code in (0,1) or account_type_code = 3) and residencyIlCount = 0 and ResidencyUSCount = 0 then 7511
when (account_type_code=2 or (account_type_code=5 and account_sub_type_code in (2,3,13,14,15,16))) and customer_id_document_type_code= 4 and i.affidavit_type_code = 2 and residence_country_code not in (212,602) then 7512
when E.Acct_Residence_Code = 12 then 7513
when Business_Ind='B' and customer_country_code = 602 and residence_country_code = 212 then 7514
when Business_Ind='B' and customer_country_code = 212 and residence_country_code = 602 then 7515
when Business_Ind='B' and customer_country_code = 602 and residence_country_code = 602 then 7516
when Business_Ind='B' and
(
(customer_country_code not in (212,602) and residence_country_code = 212)
or (residence_country_code not in (212,602) and customer_country_code = 212)
or (residence_country_code not in (212,602) and customer_country_code not in (212,602))
)
then 7518
when Acct_Ownership_Type_Code in (0,1) then 7501
when Affidavit_Sub_Type_Code in (2,3) and g.Affidavit_Type_Code = 2 and (e.account_type_code=2 or (e.account_type_code=5 and account_sub_type_code in (2,3,16))) then 7504
when Affidavit_Sub_Type_Code not in (2,3) and g.Affidavit_Type_Code = 2 and (e.account_type_code=2 or (e.account_type_code=5 and account_sub_type_code in (2,3,16))) then 7505
when account_type_code=5 and account_sub_type_code in (14,15) then 7506
when account_type_code=5 and account_sub_type_code=13 then 7508
when Business_Ind='B' then 7517
else 7519
end as ActionID,
cast(' ' as char(16)) as CustomerIdentity,
cast(' ' as char(11)) as CustomerSeq,
cast(' ' as char(20)) as CustomerFirstName,
cast(' ' as char(30)) as CustomerLastName,
cast(' ' as char(10)) as CustomerPhone
from T4890_DOCUMENT_CONTROL_RAM a
join T0120_Acct_Attribute_Calculate b on a.account_id = b.account_id
join T0101_Customer_Account_Relat c on a.account_id= c.account_id
join
(select
account_id,
count(distinct d.party_id) as PartyCount,
sum(case when d.customer_country_code = 212 then 1 else 0 end) as CitizenshipIlCount,
sum(case when d.customer_country_code = 602 then 1 else 0 end) as CitizenshipUSCount,
sum(case when d.residence_country_code = 212 then 1 else 0 end) as ResidencyIlCount,
sum(case when d.residence_country_code = 602 then 1 else 0 end) as ResidencyUSCount
from
T0101_Customer_Account_Relat c
join t0100_customer d on c.party_id = d.party_id
group by account_id) d on a.account_id = d.account_id
join gc01_account e on a.account_id = e.account_id
left outer join T0120_Account_Attribute f on a.account_id = f.account_id
left outer join T0103_Account_Affidavit g on a.account_id = g.account_id
join t0100_customer h on c.party_id = h.party_id
left outer join T0103_AFFIDAVIT_RELAT i on a.account_id = i.account_id and c.party_id = i.party_id
left outer join
(select document_control_id as DocID6 from T4890_DOCUMENT_CONTROL_RAM
where Document_Control_Status_Code = 6) as j on a.document_control_id = j.docId6
where Open_Date = #prompt('EventDate','string','( select max(open_date) from T4890_DOCUMENT_CONTROL_RAM)','date ')#
and e.division_id <> 0
and entry_branch_id <> 113
and document_control_status_code = 1
and region <> 81
and AccountBranchId in (#promptmany('SnifList','integer','737,634')#)
and DocId6 is null
)
SELECT substr( '000000070' ||
cast(year_of_calendar as varchar(4)) ||
substr('00',1,2-character_length(cast(month_of_year as varchar(2)))) || cast(month_of_year as varchar(2)) ||
substr('00',1,2-character_length(cast(day_of_month as varchar(2)))) || cast(day_of_month as varchar(2)) ||
substr('00',1,2-character_length(cast(extract(second from current_time) as varchar(2)))) || cast(extract(second from current_time) as varchar(2)) ||
substr('00',1,2-character_length(cast(extract(minute from current_time) as varchar(2)))) || cast(extract(minute from current_time) as varchar(2)) ||
'000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000' ||
'000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000' ||
'000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000' ||
'000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000' ||
'000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000' ||
'000000000000000000000000000000000000000000000',1,507)
as DataLine
from sys_calendar.calendar
where calendar_date = current_date
union all
select
concat(cast(ActionID as varchar(4)) , substring(Document_Id,character_length(Document_Id)-100,100) , '445', AccountBranchId, AccountNumber , CustomerIdentity,CustomerSeq,
CustomerFirstName, CustomerLastName, CustomerPhone, substring(Handle,character_length(Handle)-300,300)
) as DataLine
from KT
union all
select substring(concat('99999' ,
substr(
concat('0000000000',cast(count(Document_Id) as varchar(7))),
length(
concat('0000000000',cast(count(Document_Id) as varchar(7))))-9,10),
'0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
,
'0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
,
'0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
,
'0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
,
'0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
,
'0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'),1,507)
from KTASKER
Well, sorry I don`t have the query plan , I wanted to help out improve a code that an employee wrote in the past, now left the job .
what i have delete from the lines is only the string ( 'xxx ') aften then
like in lines : 12,13,14 ..etc..
what i have delete from the lines is only the string ( 'xxx ') aften then
like in lines : 12,13,14 ..etc..
You can always get a new query plan. Check the image below:
ASKER
What I have been notified now that this code written in tera-data ~ (or for a tera-data)
Not sure if there an option for the execution plan in a tera-data tool.
still A MUST to have the execution plan to be able to improve code?
Not sure if there an option for the execution plan in a tera-data tool.
still A MUST to have the execution plan to be able to improve code?
A query plan shows the indexes used by the query and which part of the query costs more. Sending a query without the data is useless so a query plan is always a must to have.
Anyway you can follow the recommendations I gave in my first post. It's equal for any RDBMS, so it will work for Teradata as well.
Anyway you can follow the recommendations I gave in my first post. It's equal for any RDBMS, so it will work for Teradata as well.
ASKER
Thank you VERY much, Well i`ll try to have the plan, and paste here, it will be after tomorrow (I`m out for a vacation tomorrow)
I am stunned that query operates at all. Does it really do that?
For example the SQL I know requires an action after THEN
it also requires an expression after the equals operator
Here is just one case expression (the column taskname) from it
By placing the string 'what?' after every missing operation or expression in that case expression I get something that is syntactically correct but which clearly needs at least 24 corrections.
For example the SQL I know requires an action after THEN
it also requires an expression after the equals operator
Here is just one case expression (the column taskname) from it
case
when account_type_code = 3 then
when Account_Attribute_Code = 826 then
when partycount = citizenshipIlCount and partycount = residencyIlCount and Account_Attribute_Code = 61 then
when (Acct_Ownership_Type_Code in (0,1) or account_type_code = 3) and h.residence_country_code = 602 then
when (account_type_code=2 or (account_type_code=5 and account_sub_type_code in (2,3,13,14,15,16))) and h.residence_country_code = 602 and customer_id_document_type_code= 4 and i.affidavit_type_code = 2 then
when (Acct_Ownership_Type_Code in (0,1) or account_type_code = 3) and residencyIlCount = 0 and ResidencyUSCount = 0 then
when (account_type_code=2 or (account_type_code=5 and account_sub_type_code in (2,3,13,14,15,16))) and customer_id_document_type_code= 4 and i.affidavit_type_code = 2 and residence_country_code not in (212,602) then
when E.Acct_Residence_Code = 12 then
when Business_Ind= and customer_country_code = 602 and residence_country_code = 212 then
when Business_Ind= and customer_country_code = 212 and residence_country_code = 602 then
when Business_Ind= and customer_country_code = 602 and residence_country_code = 602 then
when Business_Ind= and
(
(customer_country_code not in (212,602) and residence_country_code = 212)
or (residence_country_code not in (212,602) and customer_country_code = 212)
or (residence_country_code not in (212,602) and customer_country_code not in (212,602))
)
then
when Acct_Ownership_Type_Code in (0,1) then
when Affidavit_Sub_Type_Code in (2,3) and g.Affidavit_Type_Code = 2 and (e.account_type_code=2 or (e.account_type_code=5 and account_sub_type_code in (2,3,16))) then
when Affidavit_Sub_Type_Code not in (2,3) and g.Affidavit_Type_Code = 2 and (e.account_type_code=2 or (e.account_type_code=5 and account_sub_type_code in (2,3,16))) then
when account_type_code=5 and account_sub_type_code in (14,15) then
when account_type_code=5 and account_sub_type_code=13 then
when Business_Ind= then
else
end as TaskName,By placing the string 'what?' after every missing operation or expression in that case expression I get something that is syntactically correct but which clearly needs at least 24 corrections.
CASE
WHEN account_type_code = 3
THEN 'what?'
WHEN Account_Attribute_Code = 826
THEN 'what?'
WHEN partycount = citizenshipIlCount AND
partycount = residencyIlCount AND
Account_Attribute_Code = 61
THEN 'what?'
WHEN (Acct_Ownership_Type_Code IN (0, 1) OR
account_type_code = 3) AND
h.residence_country_code = 602
THEN 'what?'
WHEN (account_type_code = 2 OR
(account_type_code = 5 AND
account_sub_type_code IN (2, 3, 13, 14, 15, 16))) AND
h.residence_country_code = 602 AND
customer_id_document_type_code = 4 AND
i.affidavit_type_code = 2
THEN 'what?'
WHEN (Acct_Ownership_Type_Code IN (0, 1) OR
account_type_code = 3) AND
residencyIlCount = 0 AND
ResidencyUSCount = 0
THEN 'what?'
WHEN (account_type_code = 2 OR
(account_type_code = 5 AND
account_sub_type_code IN (2, 3, 13, 14, 15, 16))) AND
customer_id_document_type_code = 4 AND
i.affidavit_type_code = 2 AND
residence_country_code NOT IN (212, 602)
THEN 'what?'
WHEN E.Acct_Residence_Code = 12
THEN 'what?'
WHEN Business_Ind = 'what?' AND
customer_country_code = 602 AND
residence_country_code = 212
THEN 'what?'
WHEN Business_Ind = 'what?' AND
customer_country_code = 212 AND
residence_country_code = 602
THEN 'what?'
WHEN Business_Ind = 'what?' AND
customer_country_code = 602 AND
residence_country_code = 602
THEN 'what?'
WHEN Business_Ind = 'what?' AND
(
(customer_country_code NOT IN (212, 602) AND
residence_country_code = 212) OR
(residence_country_code NOT IN (212, 602) AND
customer_country_code = 212) OR
(residence_country_code NOT IN (212, 602) AND
customer_country_code NOT IN (212, 602))
)
THEN 'what?'
WHEN Acct_Ownership_Type_Code IN (0, 1)
THEN 'what?'
WHEN Affidavit_Sub_Type_Code IN (2, 3) AND
g.Affidavit_Type_Code = 2 AND
(e.account_type_code = 2 OR
(e.account_type_code = 5 AND
account_sub_type_code IN (2, 3, 16)))
THEN 'what?'
WHEN Affidavit_Sub_Type_Code NOT IN (2, 3) AND
g.Affidavit_Type_Code = 2 AND
(e.account_type_code = 2 OR
(e.account_type_code = 5 AND
account_sub_type_code IN (2, 3, 16)))
THEN 'what?'
WHEN account_type_code = 5 AND
account_sub_type_code IN (14, 15)
THEN 'what?'
WHEN account_type_code = 5 AND
account_sub_type_code = 13
THEN 'what?'
WHEN Business_Ind = 'what?'
THEN 'what?'
ELSE 'what?'
END AS TaskNameASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Paul Maxwell, look at what i wrote above :
Well, sorry I don`t have the query plan , I wanted to help out improve a code that an employee wrote in the past, now left the job .
what i have delete from the lines is only the string ( 'xxx ') aften then
like in lines : 12,13,14 ..etc..
mean I have mention the missing 'xx' after every then :}
Well, sorry I don`t have the query plan , I wanted to help out improve a code that an employee wrote in the past, now left the job .
what i have delete from the lines is only the string ( 'xxx ') aften then
like in lines : 12,13,14 ..etc..
mean I have mention the missing 'xx' after every then :}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
drtopserv, this issue has been solved?
ASKER
Sorry brother, I think I have to close the Q or accept a solution becuase I could it be able to have query plan .
anyway thanks all for trying to help out :}}
anyway thanks all for trying to help out :}}
Using functions on data fields will make the engine not using index on them if there's any.
Avoid the use of OR operator.
Can you post the actual query plan for your query?