Query plan optimization

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

avatar image By Maahi 1 asked Aug 07 at 09:35 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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

avatar image By Maahi 1 answered Aug 08 at 06:42 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x650
x5
x1

asked: Aug 07 at 09:35 AM

Seen: 48 times

Last Updated: Aug 08 at 06:42 AM