SQLTeam.com | Weblogs | Forums

How to optimize correlated subqueries?

sql2008

#1

It really bothers me how many times repeat the call to the same table but I don't know how to do it better. Would appreciate any help

SELECT --top 20

matches.provnum		
,matches.numOfCustIDs

,( select top 1 inMatches.custnmbr 
	
	from v_ourCMScustDetails inMatches with(nolock)				

	where inMatches.provnum = matches.provnum
		AND inMatches.custclas <> 'ENTERAL' 			   
		AND inMatches.CUSTCLAS <> 'ENTERAL-MED'

	order by inMatches.custIDlen

) as primaryCustID --pick ID with shortest length

,( select top 1  inMatches.userdef2
	
	from v_ourCMScustDetails inMatches with(nolock)				

	where inMatches.provnum = matches.provnum
		AND inMatches.custclas <> 'ENTERAL' 			   
		AND inMatches.CUSTCLAS <> 'ENTERAL-MED'

	order by inMatches.custIDlen

) as primaryUserdef2 --pick corp with shortest length

,( 	
	ltrim( SUBSTRING( (
	
		select ', ' + inMatches.custnmbr as [text()]
	
		from v_ourCMScustDetails inMatches with(nolock)

		where inMatches.provnum = matches.provnum
		and inMatches.custclas <> 'ENTERAL' 			   
		AND inMatches.CUSTCLAS <> 'ENTERAL-MED'

		order by inMatches.provnum
		for xml path ('')	
		 
	) ,2 ,1000 ) )

) as custList --concatenate list of IDs

FROM(

select 
	matches.provnum	
	,count( matches.custnmbr ) as numOfCustIDs

from v_ourCMScustDetails matches with(nolock)

where matches.custclas <> 'ENTERAL' 			   
AND matches.CUSTCLAS <> 'ENTERAL-MED' 

GROUP BY matches.provnum

) matches