Query optimization help

  • 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

    */

    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 ?

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

    --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
         

    Thanks in advance.

  • 1) How can we possibly help you without table definitions (including existing indexes and keys)? 

    2) Have you examined file IO stalls when this is running?

    3) Have you examined wait stats while this is running?

    4) What is the configuration of the server and especially the IO path? If you truly need all data then two things will help tremendously: RAM and IO (assuming the data is larger than the amount of RAM you can put on the server. 

    5) Speaking of RAM if it isn't maxed out for your (presumably Standard) Edition of SQL Server then make that happen. This is so important that I have actually TURNED DOWN REQUESTS FOR CONSULTING when a client didn't have enough of it!!! I am EXCEPTIONALLY GOOD at tuning SQL Server and applications that run on it, but I just cannot beat the microsecond-time-scale efficiencies that additional RAM offers. 

    6) Other things to look for are bad estimates between actual and estimated row counts in the query and mismatched data types.

    7) Oh, and are the UNIONs required? If not then replacing them with UNION ALL will remove the SORT/DISTINCT that happens under the covers with them.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Uploaded plan explorer generated query plan. IT should have the table diagram n table structure. Appreciate if any inputs from it. Tomorrow I ll try to get the table definitions if possible.

  • a couple of initial comments based on the query plan:
    -  there are a number of places that the actual number of rows far exceeds the estimated number of rows, how clean / up to date are statistics?
    -  I notice the vast majority of the joins are resolving as hash match, including many that are producing residual I/O in a table scan, this tells me there may be an opportunity to improve on indexing here.

  • Thanks Chris for taking a look.
    Wanted to know what does the term residual I/O mean? Also, is there a problem going with Hash match?

  • residual IO means that the database engine did a lot more reads than necessary, for example:
    Tax_Name_To_DDA, which is listed as a HEAP, has 4 full table scans on it.  one of those says 70,221 rows read but only 268 rows returned, another says 70,221 rows read but only 9 returned.  Here's an article that talks about residuals in execution plans:
    https://sqlperformance.com/2016/06/sql-indexes/actual-rows-read-warnings-plan-explorer

    Hash match in general is not bad, but if there are residual IO or residual predicates involved, then it means there's more work going on than necessary.
    There's also a good book available about understanding execution plans here:
    http://www.sqlservercentral.com/articles/books/94937/

  • I also notice that it has a degree of parallelism of 16 which seems high.  Guidance from Microsoft suggests this probably shouldn't be more than 8:
    https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

  • Chris. How can I tell that they were 16 threads spawned for parallelism operators in the execution plan?

  • Attaching table definitions and index information. "Table Structures.txt"

    Please give some pointers on how to go about Indexing for the base query and optimization tips.

  • vsamantha35 - Monday, August 7, 2017 11:18 PM

    Chris. How can I tell that they were 16 threads spawned for parallelism operators in the execution plan?

    If you hover your mouse pointer over the Select node of the plan diagram (the top left most node), it will show you information about the query as a whole, such as:
    Reason for early termination: Time Out
    Actual Rows: 177,474
    Estimated Rows: 56,591
    ...
    Degree of Parallelism: 16
    ...
    Branches: 26
    Used Threads: 176
    ...

  • Thanks Chris.

  • 1) I still haven't seen anything on the server configuration.

    2) There are numerous tables that have nothing but a clustered index. Some of those clustered indexes are pretty fat.

    3) There are numerous heap tables. Those have several negatives, including the potential for forwarding pointers and the fact that they can be HUGE with few rows due to the fact that deleted records that lead to empty pages result in the empty pages remaining allocated to the table. I have seen GB-sized SINGLE ROW tables due to this.

    4) Given the amount of objects in play and the complexity of the query I am going to state that this problem is one that goes beyond the free forum realm, at least for me. I recommend you get a professional in to help out.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply