CODEWITHSUNDEEP
sql-server-2008query-performance
sanar
sanar

Reputation: 447

Poorly performing SQL query

I have got a query like below, executing in SQL Server 2008

SELECT
    ipm.HEORG_REFNO,    
    ipm.HOTYP_REFNO,     
    ipm.CASLT_REFNO,     
    ipm.HOLVL_REFNO,    
    IPM.MAIN_IDENT,  
     ...  
FROM  
    dbo.HEALTH_ORGANISATIONS ipm  (NOLOCK)             
LEFT JOIN
    (SELECT
         s.heorg_refno, min(s.start_dttm) as start_dttm_SPONT, max(isnull(convert(datetime,s.end_dttm,120),convert(datetime,'9999-01-01', 120))) as end_dttm_SPONT    
     FROM
         dbo.service_points s (NOLOCK)    
     INNER JOIN
         dbo.reference_values rfval (NOLOCK) ON s.SPTYP_REFNO = rfval.RFVAL_REFNO    
                                             AND RFVAL.MAIN_CODE != 'PDT'         
     GROUP BY 
         s.heorg_refno) SPONT ON ipm.HEORG_REFNO = SPONT.HEORG_REFNO    

 -- Bring only Health Organisation records and also certain records,whose HOTYP_REFNO does not exist in REF_VALS
WHERE
    NOT EXISTS ((SELECT 'x' 
                 FROM REFERENCE_VALUES RVAL (NOLOCK) 
                 WHERE RVAL.RFVAL_REFNO = ipm.HOTYP_REFNO 
                   AND main_code IN ('011','012','015','016',  '017','019','2','AANDE','AEB','AEC','CLINIC','DAYCC','DEPRT','GPSIT','HC','HOSPL','HOST','LOCTN','LOSYN','MIU','MISC','MRL', 'SITE','THEAT','WARD','PDT','NURHM','DAYCR') 
    or ipm.HEORG_REFNO IN(select distinct HEORG_REFNO from SERVICE_POINT_SESSIONS (NOLOCK) where OWNER_HEORG_REFNO = 2001934 and HEORG_REFNO != 2001934) 
    or ipm.HEORG_REFNO IN (select REFNO from LOR_IPM_SYNTH_STG_DEV..  STAGING_Activity_LOCATION_DCS (NOLOCK) where Sources='HEORG_REFNO' and REFNO != 2001934)  
    )
  )

It takes hell a lot of time to execute the query .

When I comment the below 2 lines, it runs faster:

or ipm.HEORG_REFNO IN(select distinct HEORG_REFNO from SERVICE_POINT_SESSIONS (NOLOCK) where OWNER_HEORG_REFNO = 2001934 and HEORG_REFNO != 2001934) 
    or ipm.HEORG_REFNO IN (select REFNO from LOR_IPM_SYNTH_STG_DEV..  STAGING_Activity_LOCATION_DCS (NOLOCK) where Sources='HEORG_REFNO' and REFNO != 2001934)

Thanks for any guidance provided in tuning the query

Upvotes: 2

Views: 90

Answers (2)

Rahul
Rahul

Reputation: 77876

Try converting those IN subquery to a JOIN query like below and make sure you have proper index created on all the columns involved in join condition and where filter condition.

LEFT JOIN SERVICE_POINT_SESSIONS sps ON ipm.HEORG_REFNO = sps.HEORG_REFNO
AND sps.OWNER_HEORG_REFNO = 2001934 
AND sps.HEORG_REFNO != 2001934

I would modify your query to be like below. Though I can't do nothing about your big inlist as of now but you should pull that inlist in a table variable and consider doing a JOIN with that rather.

SELECT
    ipm.HEORG_REFNO,    
    ipm.HOTYP_REFNO,     
    ipm.CASLT_REFNO,     
    ipm.HOLVL_REFNO,    
    IPM.MAIN_IDENT,  
     ...  
FROM  
    dbo.HEALTH_ORGANISATIONS ipm            
LEFT JOIN
    (SELECT
         s.heorg_refno, min(s.start_dttm) as start_dttm_SPONT, 
         max(isnull(convert(datetime,s.end_dttm,120),convert(datetime,'9999-01-01', 120))) as end_dttm_SPONT    
     FROM
         dbo.service_points s     
     INNER JOIN dbo.reference_values rfval 
     ON s.SPTYP_REFNO = rfval.RFVAL_REFNO  
     AND RFVAL.MAIN_CODE != 'PDT'         
     GROUP BY 
         s.heorg_refno) SPONT ON ipm.HEORG_REFNO = SPONT.HEORG_REFNO

LEFT JOIN SERVICE_POINT_SESSIONS sps 
ON ipm.HEORG_REFNO = sps.HEORG_REFNO 
AND sps.OWNER_HEORG_REFNO = 2001934 
AND sps.HEORG_REFNO != 2001934

LEFT JOIN LOR_IPM_SYNTH_STG_DEV .. STAGING_Activity_LOCATION_DCS sald 
ON ipm.HEORG_REFNO = sald.HEORG_REFNO
AND sald.Sources='HEORG_REFNO' 
AND sald.REFNO != 2001934

WHERE NOT EXISTS (SELECT 1 
                 FROM REFERENCE_VALUES RVAL 
                 WHERE RVAL.RFVAL_REFNO = ipm.HOTYP_REFNO 
                 AND RVAL.main_code IN ('011','012','015','016',  '017','019','2','AANDE','AEB','AEC','CLINIC','DAYCC','DEPRT','GPSIT','HC','HOSPL','HOST','LOCTN','LOSYN','MIU','MISC','MRL', 'SITE','THEAT','WARD','PDT','NURHM','DAYCR'));

Upvotes: 1

Martin Milan
Martin Milan

Reputation: 6390

My first thoughts are that your query is massively complex - I would be looking at ways to simplify it...

In clauses don't always perform well - I would be tempted to suck this info into a table variable of banned "main_Codes", left join to it and test for null...

Time to run the Execution Plan though and see where your bottle necks actually are, which will depend on your own environment (indexing, stats etc)...

Upvotes: 2

Related Questions