SQL Server Performance

MAX in where clause

Discussion in 'T-SQL Performance Tuning for Developers' started by rklimes, Apr 9, 2007.

  1. rklimes New Member

    What is the best way to have MAX (or any aggregate function) in where clause? I am using the query below but performance is really bad. I would preferably like to not use subquery.

    SELECT B.BO_ID, B.CM_PURPOSE_TYPE_ID, D.DESCR, C.PHONE
    FROM PS_BO_CM AS B
    INNER JOIN PS_BO_CM_USE AS E ON B.BO_ID = E.BO_ID
    AND B.PROFILE_CM_SEQ = E.PROFILE_CM_SEQ
    INNER JOIN PS_CM AS C ON B.CM_ID = C.CM_ID
    INNER JOIN PS_CM_PURP_TYPE AS D ON B.CM_TYPE_ID = D.CM_TYPE_ID
    AND B.CM_PURPOSE_TYPE_ID = D.CM_PURPOSE_TYPE_ID
    WHERE (C.CM_TYPE_ID = 2)
    AND (C.PHONE <> ' ')
    AND (E.CM_USE_START_DT =
    (SELECT MAX(CM_USE_START_DT) AS Expr1
    FROM PS_BO_CM_USE AS E1
    WHERE (BO_ID = E.BO_ID)
    AND (PROFILE_CM_SEQ = E.PROFILE_CM_SEQ)
    AND (CM_USE_START_DT <= GETDATE())
    )
    ) AND (E.PRIMARY_IND = 'Y')
  2. FrankKalis Moderator

    I might be missing something but this is one of the two common ways to get the latest entry <= a target date. The other one would be to turn this into a JOIN operation.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. rklimes New Member

    Thanks Frank. Using a join greatly reduced running time.

Share This Page