Query optimisation

Transact-SQL
1

need alternative of query
isnull(OrgGen.[CustomerNKey],-99) = isnull(COALESCE(AC.CustomerNKey,ORGGeneral.CustomerNKey),-99)

2

That's not a query, it's a boolean expression. Does it appear in a WHERE clause? A CASE expression? An IIF() call?

Please post the entire query you're trying to tune.

3

(OrgGen.[CustomerNKey] IS NULL AND AC.CustomerNKey IS NULL AND ORGGeneral.CustomerNKey IS NULL)
OR
(OrgGen.[CustomerNKey] = AC.CustomerNKey OR OrgGen.[CustomerNKey] = ORGGeneral.CustomerNKey)

4 
WHERE 
	OrgGen.CustomerNKey = AC.CustomerNKey
	OR
	OrgGen.CustomerNKey = ORGGeneral.CustomerNKey
	OR 
	(
		OrgGen.CustomerNKey IS NULL
		AND AC.CustomerNKey IS NULL
		AND ORGGeneral.CustomerNKey
	)
5

this is and condition in where cluse there are also other cluse
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.CustomerNKey),-99)  
			  )

AND LEN(R1.ORGNAME)>0

6

thank you for response.it is appear in where cluse with and condition

and isnull(OrgGen.[CustomerNKey],-99) = isnull(COALESCE(AC.CustomerNKey,ORGGeneral.CustomerNKey),-99)

7

thanku for response.can we use these 2 statement simultanousluy