Slow Query

SELECT top 10 ISNULL(First_Name, '' ) First_Name, ISNULL(Last_Name, '') Last_Name,ISNULL(ADDR1, '') ADDR1, ISNULL(AARP_No, '') AARP_No,p.Prospect_Id
FROM Prospects p INNER JOIN Prospect_Addr a ON p.Prospect_Id = a.Prospect_Id and primary_Addr_Id = prospect_Addr_Id
WHERE p.Removal_YN='N' and
isnull( ltrim(rtrim(First_Name)), '') like '%' AND
isnull( ltrim(rtrim( Last_Name)),'') like '%' AND
isnull( ltrim(rtrim(ADDR1)), '') like '%' AND
isnull( ltrim(rtrim(AARP_No)), '') LIKE '%' AND p.Prospect_Id NOT IN (SELECT top 10 p.Prospect_Id
FROM Prospects p INNER JOIN Prospect_Addr a ON p.Prospect_Id = a.Prospect_Id and primary_Addr_Id = prospect_Addr_Id
WHERE p.Removal_YN='N' and
isnull( ltrim(rtrim(First_Name)), '') like '%' AND
isnull( ltrim(rtrim(Last_Name)), '') like '%' AND
isnull( ltrim(rtrim(ADDR1)), '') like '%' AND
isnull( ltrim(rtrim(AARP_No)), '') LIKE '%' ORDER BY First_Name, Last_Name, ADDR1)
ORDER BY First_Name, Last_Name, ADDR1

Both tables of the Select has 5 million records each , The query took about 15 minutes.I am looking for your help in rewriting this query only.Please do not advice on using Update Statictics or Indexes.

Thanks

raj


raj

What is this actually trying to do? it looks like it is getting everything except the top 10 rows where removalyn = n

if so, then doing that client side would make much more sense...?

also the isnull... = '%' clauses don't do anything and will slow the statment down

finally indexing
- prospects( removalyn, prospectid, prospect_addr_id )
- prospect_addr( prospect_add, primary_addr_id )

Cheers
Twan

The Inner query get top 10 prospectId rows and the Outer query gets top 10 rows,Which are Not
of the Sub-Query.How can I avoid using "NOT IN" Operator.

Thanks

raj

you could select the top 20 and on the client ignore the first 10 rows...?

what do you expect the other parts of the where clause to do though? isnull( col, '' ) like '%' is all rows so why is it there?

Cheers
Twan

in addition to the other replies :
- why the extra rtrim ?

if symantics of NULL and blanc is equal avoid nullable columns

you could use datalength(ltrim(yourcolumn)) > 0