>The biggest hit is in the clustered index scan on the Deceased table at 82% cost.

SQL Server is doing a table scan (the clustered index scan) because Deceased.Zip is not selective enough

-Mike


>The biggest hit is in the clustered index scan on the Deceased table at 82% cost. Going back to the size of the return rows, I ran it against one of the bigger campaign that returns 2.5 million matching rows and it took close to 7 minutes to finish. It also used up 727MB (you read right, almost a gig of space) on my local HD.
>
>Like I mentioned in my original post, perhaps it's the size and number of the rows that is returned and not the optimization of the query itself that is making this query so slow. Bad part is, I need all records that is being pulled.
>
>>Start with the query plan. What does it look like?
>>
>>>> am I doomed 'cause of amount of records returned?
>>
>>one million rows is a lot to collect and bring across the wire.
>>
>>-Mike
>>
>>>First, I have a table called "Deceased" that holds over 6 million names and addresses of deceased persons. I need to do a JOIN on "ZipCross" table that holds all the zipcodes that were mailed for a particular direct mail campaign. Here is my SQL statement:
>>>
>>>SELECT Deceased.* FROM Deceased JOIN ZipCross ON Deceased.Zip=Zipcross.Zip
>>>JOIN AreaOrder C ON B.AreaID=C.AreaID AND C.CampaignID=300
>>>
>>>The problem is that this query takes almost 5 minutes to complete. Of course it doesn't help that the resulting recordset is over one million records. Anyone with ideas to make this query faster or am I doomed 'cause of amount of records returned? TIA.