SQL Server Performance

Slow Query

Discussion in 'Performance Tuning for DBAs' started by krajdba, Oct 21, 2004.

  1. krajdba New Member

    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
  2. FrankKalis Moderator

    Well, what do you expect from this query apart from table scans?



    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  3. Twan New Member

    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
  4. krajdba New Member

    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
  5. Twan New Member


    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
  6. FrankKalis Moderator

  7. alzdba Member

    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

Share This Page