Query plan optimization

Maahi 2017-08-07 09:35:01

Hi Experts,

One of the query is taking long time to execute. Its taking more than 6 mins.
Can anyone provide guidance on optimize this query and bring down the execution time.
Ran the query in plan explorer and attaching the same.

— Rowcount info
/*
TableName SchemaName RowCounts
DDA_Acct dbo 70224
VDDA_RELATIONSHIP dbo 177474 —this is a View
Branch_Description dbo 56
CIF_Name dbo 245108
NAICS_Description dbo 2213
CIF_Contact dbo 83429
CIF_Phone dbo 333109

*/

–This is internally calling one view. Definition is below.

The issue is there are no where /filter condition as we have to take all the data from all tables as per bisuness rule.
so, basically the tables will be loaded on daily basis and we need to consider all the rows for processing.
That is the reason why we see full table scans / Index Scans all over the execution plan.

So, any recommendations in regards the query rewrite, any index recommendations against the tables which are getting joined? any tips on tsql rewrite ?(case rewrite with joins etc…)
or any other way we can reduce the logical reads etc… Right now the query is going in parallel . Any suggestions over MAXDOP setting ?

CREATE VIEW dbo.VDDA_Relationship AS
SELECT
A.Institution_Number,
A.Portfolio,
A.DDA_Account,
A.Account_Type,
SA.Port_Sequence_Number AS Address_Line,
A.Branch_Number,
CN.Name,
CN.Name_ID,
Inq.Port_Sequence_Number AS Name_Line,
CASE
WHEN
TN.Name_ID IS NOT NULL
THEN
'Yes'
ELSE
"
END
AS Tax,
CASE
WHEN
Inq.Direct_Indirect_Code = 0
THEN
'Direct'
WHEN
Inq.Direct_Indirect_Code = 1
THEN
'Direct'
WHEN
Inq.Direct_Indirect_Code = 2
THEN
'Indirect'
WHEN
Inq.Direct_Indirect_Code = 3
THEN
'Secondary'
ELSE
"
END
AS Direct_Indirect_Code, CN.Relationship_Code AS Name_Format_Code, 3 AS Relationship_Code, 'Owner/Signer' AS Relationship_Code_Desc,
(
Inq.Relationship_Percent * 100
)
AS Relationship_Percent, Inq.CIF_Alt_Name_Key
FROM
dbo.DDA_Acct A
LEFT OUTER JOIN
dbo.Stmt_Address_To_DDA SA
ON A.DDA_Account = SA.Account_Number
AND A.Institution_Number = SA.Institution_Number
INNER JOIN
dbo.Inquiry_Name_To_DDA Inq
ON A.DDA_Account = Inq.Account_Number
AND A.Institution_Number = Inq.Institution_Number
INNER JOIN
dbo .CIF_Name CN
ON Inq.Name_ID = CN.Name_ID
AND Inq.Institution_Number = CN.Institution_Number
LEFT OUTER JOIN
dbo.Tax_Name_To_DDA TN
ON Inq.Account_Number = TN.Account_Number
AND Inq.Institution_Number = TN.Institution_Number
AND Inq.Name_ID = TN.Name_ID
LEFT OUTER JOIN
dbo.Owner_Signer_Name_To_DDA Own
ON Inq.Account_Number = Own.Account_Number
AND Inq.Institution_Number = Own.Institution_Number
AND Inq.Name_ID = Own.Name_ID
LEFT OUTER JOIN
dbo.Relationship_Name_to_DDA R
ON Inq.Account_Number = R.Account_Number
AND Inq.Institution_Number = R.Institution_Number
AND Inq.NAME_ID = R.Name_ID
LEFT OUTER JOIN
dbo.Name_to_Port NP
ON A.Portfolio = NP.Portfolio
AND Inq.Name_id = NP.Name_ID
AND Inq.Port_Sequence_Number = NP.Port_Sequence_Number
AND Inq.Institution_Number = NP.Institution_Number
AND NP.Name_Line <> 999
LEFT OUTER JOIN
dbo.Port_Relationship PR
ON NP.Relationship_Code = PR.Relationship_Code
AND NP.Institution_Number = PR.Institution_Number
WHERE
Inq.Name_Line <> 999
AND Own.Name_ID IS NULL
AND R.Name_ID IS NULL
UNION
SELECT
A.Institution_Number,
A.Portfolio,
A.DDA_Account,
A.Account_Type,
SA.Port_Sequence_Number AS Address_Line,
A.Branch_Number,
CN.Name,
CN.Name_ID,
Own.Port_Sequence_Number AS Name_Line,
CASE
WHEN
TN.Name_ID IS NOT NULL
THEN
'Yes'
ELSE
"
END
AS Tax,
CASE
WHEN
Own.Direct_Indirect_Code = 0
THEN
"
WHEN
Own.Direct_Indirect_Code = 1
THEN
'Direct'
WHEN
Own.Direct_Indirect_Code = 2
THEN
'Indirect'
WHEN
Own.Direct_Indirect_Code = 3
THEN
'Secondary'
ELSE
"
END
AS Direct_Indirect_Code, CN.Relationship_Code AS Name_Format_Code, Own.Relationship_Code AS Relationship_Code, ComRel.External_Value AS Relationship_Code_Desc,
(
Own.Relationship_Percent * 100
)
AS Relationship_Percent, Own.CIF_Alt_Name_Key
FROM
dbo.DDA_Acct A LEFT
OUTER JOIN
dbo.Stmt_Address_To_DDA SA
ON A.DDA_Account = SA.Account_Number
AND A.Institution_Number = SA.Institution_Number
INNER JOIN
dbo.Owner_Signer_Name_To_DDA Own
ON A.DDA_Account = Own.Account_Number
AND A.Institution_Number = Own.Institution_Number
LEFT OUTER JOIN
dbo.Tax_Name_To_DDA TN
ON Own.Account_Number = TN.Account_Number
AND Own.Institution_Number = TN.Institution_Number
AND Own.Name_ID = TN.Name_ID
LEFT OUTER JOIN
dbo.Name_to_Port NP
ON A.Portfolio = NP.Portfolio
AND Own.Name_id = NP.Name_ID
AND Own.Port_Sequence_Number = NP.Port_Sequence_Number
AND Own.Institution_Number = NP.Institution_Number
AND NP.Name_Line <> 999
LEFT OUTER JOIN
dbo.Port_Relationship PR
ON NP.Relationship_Code = PR.Relationship_Code
AND NP.Institution_Number = PR.Institution_Number
INNER JOIN
dbo.CIF_Name CN
ON Own.Name_ID = CN.Name_ID
AND Own.Institution_Number = CN.Institution_Number
INNER JOIN
dbo.Common_Relationship ComRel
ON Own.Relationship_Code = ComRel.Relationship_Code
AND Own.Institution_Number = ComRel.Institution_Number
UNION
SELECT
A.Institution_Number,
A.Portfolio,
A.DDA_Account,
A.Account_Type,
SA.Port_Sequence_Number AS Address_Line,
A.Branch_Number,
CN.Name,
CN.Name_ID,
R.Port_Sequence_Number AS Name_Line,
CASE
WHEN
TN.Name_ID IS NOT NULL
THEN
'Yes'
ELSE
"
END
AS Tax,
CASE
WHEN
R.Direct_Indirect_Code = 0
THEN
"
WHEN
R.Direct_Indirect_Code = 1
THEN
'Direct'
WHEN
R.Direct_Indirect_Code = 2
THEN
'Indirect'
WHEN
R.Direct_Indirect_Code = 3
THEN
'Secondary'
ELSE
"
END
AS Direct_Indirect_Code, CN.Relationship_Code AS Name_Format_Code, R.Relationship_Code, DR.External_Value AS Relationship_Code_Desc,
(
R.Relationship_Percent * 100
)
AS Relationship_Percent, R.CIF_Alt_Name_Key
FROM
dbo.DDA_Acct A
LEFT OUTER JOIN
dbo.Stmt_Address_To_DDA as SA
ON A.DDA_Account = SA.Account_Number
AND A.Institution_Number = SA.Institution_Number
INNER JOIN
dbo.Relationship_Name_to_DDA R
ON A.DDA_Account = R.Account_Number
AND A.Institution_Number = R.Institution_Number
INNER JOIN
dbo.CIF_Name CN
ON R.Name_ID = CN.Name_ID
AND R.Institution_Number = CN.Institution_Number
INNER JOIN
dbo.DDA_Relationship DR
ON R.Relationship_Code = DR.Relationship_Code
AND R.Institution_Number = DR.Institution_Number
LEFT OUTER JOIN
dbo.Tax_Name_To_DDA TN
ON R.Account_Number = TN.Account_Number
AND R.Institution_Number = TN.Institution_Number
AND R.Name_ID = TN.Name_ID
WHERE
R.Relationship_Code > 99
UNION
SELECT
A.Institution_Number,
A.Portfolio,
A.DDA_Account,
A.Account_Type,
SA.Port_Sequence_Number AS Address_Line,
A.Branch_Number,
CN.Name,
CN.Name_ID,
R.Port_Sequence_Number AS Name_Line,
CASE
WHEN
TN.Name_ID IS NOT NULL
THEN
'Yes'
ELSE
"
END
AS Tax,
CASE
WHEN
R.Direct_Indirect_Code = 0
THEN
"
WHEN
R.Direct_Indirect_Code = 1
THEN
'Direct'
WHEN
R.Direct_Indirect_Code = 2
THEN
'Indirect'
WHEN
R.Direct_Indirect_Code = 3
THEN
'Secondary'
ELSE
"
END
AS Direct_Indirect_Code, CN.Relationship_Code AS Name_Format_Code, R.Relationship_Code, ComRel.External_Value AS Relationship_Code_Desc,
(
R.Relationship_Percent * 100
)
AS Relationship_Percent, R.CIF_Alt_Name_Key
FROM
dbo.DDA_Acct A
LEFT OUTER JOIN
dbo.Stmt_Address_To_DDA SA
ON A.DDA_Account = SA.Account_Number
AND A.Institution_Number = SA.Institution_Number
INNER JOIN
dbo.Relationship_Name_to_DDA R
ON A.DDA_Account = R.Account_Number
AND A.Institution_Number = R.Institution_Number
INNER JOIN
dbo.CIF_Name CN
ON R.Name_ID = CN.Name_ID
AND R.Institution_Number = CN.Institution_Number
INNER JOIN
dbo.Common_Relationship ComRel
ON R.Relationship_Code = ComRel.Relationship_Code
AND R.Institution_Number = ComRel.Institution_Number
LEFT OUTER JOIN
dbo.Tax_Name_To_DDA TN
ON R.Account_Number = TN.Account_Number
AND R.Institution_Number = TN.Institution_Number
AND R.Name_ID = TN.Name_ID
WHERE
R. Relationship_Code > 3

  -- takes  42 secs to the above view

Thank you.
link text

Maahi 2017-08-08 06:42:58
Adding table definitions and index info.
Please give some pointers on how to go about Indexing for the base query and optmization tips.

link text