Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Red face Unanswered: MSSQL2000-Self-Join Query Tuning help

    SELECT * FROM TA a WHERE a.rx=264886 and
    AN= (select max(AN) FROM TA where rx=a.rx)

    I have a table TA with 8+ million rows and there is clustered PK on (rx, AN) columns. The count on rx=264886 is 6000+ rows. This query takes about 1 to 2 minutes to fetch data. Can anyone suggest how to improve performance and fetch data faster?

    Thanks, Vinnie

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    I'd say:
    SELECT TOP 1 * FROM TA WHERE rx=264886
    ORDER BY an DESC

    <edit>
    That only works if there is only one row with an = max(an).

  3. #3
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Red face Thank you but

    that statement does not include the max(AN) for rn value. and when i just added "top 1" it did not improve any performance at all with an=(select max(an) ....) clause. Any other ideas?

    Thanks, Vinnie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •