SQL Server Performance

Optimize this sql query

Discussion in 'Getting Started' started by Varsha, Jul 1, 2010.

  1. Varsha New Member

    I have this query thats taking almost 20sec to complete. we need to optimize this to at least reduce the run time from 20sec to 4-5sec. I have already tried putting indexes on the columns which are used in joins but that didnt improve anything much. Can you please suggest anything that we can do to improve it further.
    DECLARE @StartModifiedDate DATETIME
    SELECT @StartModifiedDate = CONVERT(DATETIME,(DATEADD(MM,-24,(CONVERT(CHAR(8),GETDATE(),102) + '01')))) SELECT DISTINCT H
    .HierarchyID AS AssociateHierarchyID ,H.LastName AS AssociateLastName
    ,H.FirstName AS AssociateFirstName ,CASE
    WHEN H.FirstName IS NULL THEN H.LastName ELSE H.LastName + ',' + H.FirstName
    END AS AssociateName ,HMGR.HierarchyId AS ManagerHierarchyID
    ,HMGR.LastName AS ManagerLastName ,HMGR.FirstName AS ManagerFirstName
    ,CASE WHEN HMGR.FirstName IS NULL THEN HMGR.LastName
    ELSE HMGR.LastName + ',' + HMGR.FirstName END AS ManagerName
    ,CASE WHEN LEN(REPLACE(SUBSTRING(CT.HierarchyCode, 1, 7), '.', '')) = 7 THEN SUBSTRING(CT.HierarchyCode, 1, 7) END SevenDot ,CASE WHEN LEN(REPLACE(SUBSTRING(CT.HierarchyCode, 1, 6), '.', '')) = 6 THEN SUBSTRING(CT.HierarchyCode, 1, 6) END SixDot
    ,CASE WHEN LEN(REPLACE(SUBSTRING(CT.HierarchyCode, 1, 5), '.', '')) = 5 THEN SUBSTRING(CT.HierarchyCode, 1, 5) END FiveDot ,RTH.HierarchyCode Region
    ,CTLOB.CMDBLobTypeID ,RTH.Description
    ,HMGR.Active AS ActiveManager ,H.Active AS ActiveAssociate
    FROM (SELECT DISTINCT CreditTakerID, CASE WHEN ManualHierarchyCode IS NULL OR LEN(ManualHierarchyCode) = 0 THEN HierarchyCode ELSE ManualHierarchyCode END HierarchyCode
    FROM CreditTaker(NOLOCK) WHERE LEN((CASE WHEN ManualHierarchyCode IS NULL OR LEN(ManualHierarchyCode) = 0 THEN HierarchyCode ELSE ManualHierarchyCode END)) <> 0) CT
    INNER JOIN LineofBusinessHierarchyProcessType LBTH ON LBTH.HierarchyCode = SUBSTRING(CT.HierarchyCode, 1, LEN(LBTH.HierarchyCode)) AND LBTH.Active = 'A' INNER JOIN CMDBRegionTypeHierarchy RTH ON RTH.HierarchyCode = SUBSTRING(CT.HierarchyCode, 1, 3) AND RTH.Active='A'
    INNER JOIN CMDbLOBType CTLOB ON CTLOB.LobTypeID = LBTH.LineofBusinessTypeId AND RTH.CMDBLobTypeID = CTLOB.CMDBLobTypeID AND CTLOB.ACTIVE='A' INNER JOIN Package(NOLOCK) P on P.CreditTakerID = CT.CreditTakerID
    INNER JOIN Request(NOLOCK) REQ on REQ.PackageID = P.PackageID AND P.Active = 'A' INNER JOIN Responsibility(NOLOCK) RES on RES.RequestId = REQ.RequestId
    INNER JOIN Hierarchy H ON H.HierarchyID = RES.CustodianHierarchyID AND H.UltimateHierarchyID = 40920 AND (H.Active='A' OR (H.Active ='I' AND H.Modified > @StartModifiedDate))
    INNER JOIN Hierarchy HMGR ON H.RealParentID = HMGR.HierarchyID and HMGR.FirstName IS NOT NULL AND HMGR.LastName IS NOT NULL
    AND (HMGR.Active='A' OR (HMGR.Active ='I' AND HMGR.Modified > @StartModifiedDate))
  2. FrankKalis Moderator

    Can you please post the table structures and the existing indices? Also, the execution plan would be nice to have.
  3. satya Moderator

    Also make sure to specify the volume on the tables that are involved in this script, as referred you can see the estimated execution plan in Query editor that can specify the numer of rows it is returning.
  4. Adriaan New Member

    I notice both the derived table and the main query have DISTINCT. If you can drop either or both, that would have an impact already.
    The joining on SUBSTRING expressions means that indexes on those columns will go ignored. The "hierarchy codes" may seem handy, but they're going against all normalization rules, and are very likely to be the main cause of the performance issue.
    You could add a temp table for those hierarchy codes, where you put the hierarchy code and the substring'ed bits each in a separate column, then join on this temp table.
    Most of the filter criteria would be better placed in a WHERE clause - I wouldn't put it all in the JOIN expressions (except if it affects the results).
    Final thing to check: is the HMGR.Modified column actually a DATETIME column?
  5. Varsha New Member

    Thanks Adriaan i will try to optimize the query with what you have suggested hopefully that should improve the performance little bit.

Share This Page