Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 optimization help on query accepting user params

Author  Topic 

mushu
Starting Member

4 Posts

Posted - 2008-08-20 : 16:10:41
I took a look at the execution plan but I don't understand what it is saying. :-( Looking for suggestions as to where I can focus for speeding this puppy up. The WHERE clause has negligible effect on the query, the bulk of time is spent doing something with the index searches which I thought was supposed to be fast. I know you don't have the data but perhaps looking at the code will trigger a thought. Let me know if I need to provide more info:

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 04:52:26
Hi Mushu,

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)

-------------
Charlie
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 04:55:11
( Sorry Scans are probably undesirable unless the vast majority of the rows scanned actually do qualify for your conditions.)

-------------
Charlie
Go to Top of Page

mushu
Starting Member

4 Posts

Posted - 2008-08-21 : 12:37:36
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)
Hmmm, too bad Query Analyzer has no way to save the plan as a file or image...Photoshop ftw! I did it as both a text version and a graphic version. On the graphic one I hovered over the highest percentage cost item to pop open the statistics window, for your viewing pleasure. These files will be up until Friday night.
[url]http://agr.wa.gov/PMLaddrIns_plan.jpg[/url]
[url]http://agr.wa.gov/PMLaddrIns_plan.txt[/url]

Just a shot in the dark: would it be faster for me to do a UNION of mailing and physical addresses, and somehow magically have it insert the physical only if the mailing doesn't already exist?
Go to Top of Page

mushu
Starting Member

4 Posts

Posted - 2008-08-21 : 16:52:08
That might be too hard to deal with, so here is a more generalized question based on my specific code previously:

I'm looking for a fast method to create a temp table of address info for every customer in a database, where I want only mailing addresses UNLESS they have no mailing address, in which case I want the physical address. The database unfortunately did not duplicate address data for mailing and physical, else this would be easy.

Any common query code that does this more generic activity?

--Edit:

Ok, just discovered the horrors of arbitrary use of the DISTINCT clause. Removing the "outer" (top) DISTINCT will drop execution time down to 2 seconds! The same number of rows are returned. I think I will go with this solution and call it good.
Go to Top of Page
   

- Advertisement -