Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2017
    Posts
    2

    Question Unanswered: performance optimisation

    need a help in query opimisation in sqlserver


    And ISnULl(Org.CustomernKey,-99=ISNUll(ISNULL(Stg.CustomernKey,Org.CustomerNKey ),-99)


    this query is taking maximum time in production
    1.Query define in where cluse with and condition
    2.It assign data from stg and mail table to main table
    3.ISNull condition taking more time on this query and need to restructure the cluse and replacement of isnull


    can you help me out with restucturing in sqlserver

  2. #2
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    Pretty sure with what you gave us above the answer is on page 173.

    If you really want help give us some additional info one AND condition from the query just doesn't cut it. need the query, table/index structures, current access plan...

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,863
    Provided Answers: 17
    The code above would throw a number of syntax violations (The main one is that you are missing an end parenthesis on the left side of the "="), and not just for the mixed cases of the ISNULL statements. For nested ISNULL statements, you may want to invest in the COALESCE statement, which would be easier to read:

    Code:
    AND COALESCE(Org.CustomerNKey, -99) = COALESCE(Stg.CustomerNKey, Org.CustomerNKey, -99)
    It won't make the code faster, but it may keep future developers from cursing you after they try to read this code.

  4. #4
    Join Date
    Aug 2017
    Posts
    2

    optimisation

    Quote Originally Posted by MCrowley View Post
    The code above would throw a number of syntax violations (The main one is that you are missing an end parenthesis on the left side of the "="), and not just for the mixed cases of the ISNULL statements. For nested ISNULL statements, you may want to invest in the COALESCE statement, which would be easier to read:

    Code:
    AND COALESCE(Org.CustomerNKey, -99) = COALESCE(Stg.CustomerNKey, Org.CustomerNKey, -99)
    It won't make the code faster, but it may keep future developers from cursing you after they try to read this code.

    WHERE NOT EXISTS (SELECT 1

    FROM [OCC].[dbo].[OrgGeneralInfo] AS OrgGen

    --Inner join to include last valid row per key

    INNER JOIN (SELECT [OrganisationID]

    , MAX(ValidFromDate) MAXValidFromDate

    FROM OCC.dbo.ORGGeneralInfo

    GROUP BY [OrganisationID] ) AS ORGMax

    ON ORGMax.OrganisationID = OrgGen.OrganisationID

    AND ORGMax.MAXValidFromDate = OrgGen.ValidFromDate

    WHERE OrgGen.[OrganisationID] = Org.[OrganisationID]

    AND OrgGen.[KknrID] = KR.[KknrID]

    AND OrgGen.[OrganisationNo] = R1.[OrgNo]

    AND OrgGen.[Name] = R1.[OrgName]

    AND OrgGen.[CustomerSegmentNKey] =
    R1.[CustomerSegmentNKey]
    AND OrgGen.[CustomerSegment] = R1.[CustomerSegment]

    AND OrgGen.[SectorNKey] = R1.[SectorNKey]

    AND OrgGen.[Sector] = R1.[Sector]

    AND OrgGen.[SalesUnitNKey] = R1.[SalesUnitNKey]

    AND OrgGen.[SalesUnit] = R1.[SalesUnit]

    AND OrgGen.[SalesTeamNKey] = R1.[SalesTeamNKey]

    AND OrgGen.[SalesTeam] = R1.[SalesTeam]

    AND OrgGen.[SalesPersonNKey] = R1.[SalesPersonNKey]

    AND OrgGen.[SalesPerson] = R1.[SalesPerson]

    AND OrgGen.[SegmentNKey] = R1.[SegmentNKey]

    AND OrgGen.[Segment] = R1.[Segment]

    AND OrgGen.[IndustryNKey] = R1.[IndustryNKey]

    AND OrgGen.[Industry] = R1.[Industry]

    AND OrgGen.[AccountTypeNKey] = R1.[AccountTypeNKey]

    AND OrgGen.[AccountType] = R1.[AccountType]

    AND ISNULL(OrgGen.[CustomerNKey],-99) = ISNULL(ISNULL(AC.CustomerNKey,ORGGeneral.CustomerN Key),-99)
    --and (OrgGen.[CustomerNKey] IS NULL AND AC.CustomerNKey IS NULL AND ORGGeneral.CustomerNKey IS NULL)
    -- and OrgGen.CustomerNKey = AC.CustomerNKey

    need optimisation of AND ISNULL(OrgGen.[CustomerNKey],-99) = ISNULL(ISNULL(AC.CustomerNKey,ORGGeneral.CustomerN Key),-99) statement and insted of not exists alternative
    Last edited by rajaram_2; 08-26-17 at 05:12. Reason: provide info

  5. #5
    Join Date
    Jan 2017
    Posts
    18
    thank you for sharing great code for improve the performance.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •