CREATE TABLE #PMLaddrIns (
	  AddressTypeCode smallint , CustomerID int, AddressID int
	, AddrLine1 varchar(50), AddrLine2 varchar(50), AddrCity varchar(35), AddrZip5 int
	, AddrCountry varchar(20), AddrZipForeign varchar(15)
	, CountyDesc varchar(35), CountyCode int, Abbrev varchar(5), StateCode int 
)

declare @CountyCode int , @statecode int -- parameters passed in from user via webpage
set @CountyCode=-1 -- all of WA
set @statecode=181 -- 181=WA

INSERT INTO #PMLaddrIns
SELECT DISTINCT 
	  AddressTypeCode , CustomerID , AddressID 
	, COALESCE(AddrLine1,'Null') AS AddrLine1 , COALESCE(AddrLine2,'Null') AS AddrLine2 
	, COALESCE(AddrCity,'Null')  AS AddrCity , COALESCE(AddrZip5,'99999') AS AddrZip5 
	, AddrCountry , AddrZipForeign 
	, COALESCE(CSL.CountyDesc,'n/a') AS CountyDesc , CSL.CountyCode , CSL.Abbrev , CSL.StateCode
FROM (
	SELECT DISTINCT 
		  Addr.AddressTypeCode , Cu.CustomerID , Addr.AddressID , 
		  AddrLine1 , AddrLine2 , AddrCity , AddrZip5 
		, AddrCountry , AddrZipForeign , Addr.StateCode , Addr.CountyCode
	FROM Customer Cu
	INNER JOIN CustomerAddrMgr CAdM ON CAdM.CustomerID = Cu.CustomerID
	INNER JOIN Address Addr ON Addr.AddressID = CAdM.AddressID
	WHERE CAdM.DeletedDTS IS NULL -- only want current mailing addresses, only use physical addr if no mailing
		AND ( Addr.AddressTypeCode = 1 -- 1=mailing
			OR ( Addr.AddressTypeCode = 3 AND CAdM.CustomerID NOT IN ( -- 3=physical
				SELECT CAdrM.CustomerID
				FROM CustomerAddrMgr CAdrM
				INNER JOIN Address Adr ON Adr.AddressID = CAdrM.AddressID
				WHERE Adr.AddressTypeCode = 1 ) -- 1=mailing
			)
		)
) AddressInfo -- left outer join since some addresses may not be in CSL table below
LEFT OUTER JOIN CountyStateLookupFromPMForWeb CSL -- holds mapping of city, county, state relationships
	ON CSL.CountyCode = AddressInfo.CountyCode AND CSL.StateCode = AddressInfo.StateCode
	where (
	     ( @CountyCode = 0 ) -- anywhere
	    OR
	     ( @CountyCode =-1 AND CSL.StateCode = 181 ) -- all of WA
	    OR
	     ( @CountyCode > 0 AND CSL.CountyCode=@CountyCode AND CSL.StateCode=COALESCE(@StateCode, CSL.StateCode) ) -- user selected county
	    )

select * from #PMLaddrIns
drop table #PMLaddrIns

-- 19 secs, 31274 rows : with where clause
-- 18 secs, 33642 rows : without where clause

quote:

---

Originally posted by Transact Charlie
It would be a help if you could post your execution plan,
Will show a where your query is performing: index seek (good - fast) and index scan (bad - slow)

---