SQLTeam.com | Weblogs | Forums

Need optimisation as query getting vary slow at ISNULL section and also replacement of is null section with other alternative.and alternavie of not exists


#1

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 (OrgGen.[CustomerNKey] IS NULL AND AC.CustomerNKey IS NULL AND ORGGeneral.CustomerNKey IS NULL)
--			  and OrgGen.CustomerNKey = AC.CustomerNKey

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

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

                          )

AND LEN(R1.ORGNAME)>0need optimisation


#2

Please post:
-Create table DDL
-Inserts of sample data.
-Entire query
-Desired result
Thank you


#3

thank you sir for response. i will provide entire query and sample data with table structure