SQL Server Performance

Which query is best and why?

Discussion in 'General Developer Questions' started by haridba, Nov 12, 2007.

  1. haridba New Member

    Hi all
    The following two queries are givin the same result.but the last query returns rows quicker than the first one.
    can some one tell me the difference in the logic
    SELECT E.ENTITLEMENT_ID,EP.ENT_PARTY_ID ,EP.PARTY_ROLE FROM
    ENTITLEMENT E
    LEFT OUTER JOIN
    ENT_PARTY EP
    ON E.ENTITLEMENT_ID = EP.ENTITLEMENT_ID
    AND EP.PARTY_ROLE = 'ENDCO',
    CONTRACT_SITE,
    PRODUCT
    WHERE (E.PRODUCT_ID = PRODUCT.PRODUCT_ID)
    AND (E.CONTRACT_SITE_ID = CONTRACT_SITE.CONTRACT_SITE_ID)
    AND UPPER(CONTRACT_SITE.SITE_PUB_ID) = N'PTA18DEC200616619')
    ORDER BY E.ENTITLEMENT_ID
    SELECT E.ENTITLEMENT_ID,EP.ENT_PARTY_ID ,EP.PARTY_ROLE FROM
    ENTITLEMENT E
    LEFT OUTER JOIN
    ENT_PARTY EP
    ON E.ENTITLEMENT_ID = EP.ENTITLEMENT_ID,
    CONTRACT_SITE,
    PRODUCT
    WHERE (E.PRODUCT_ID = PRODUCT.PRODUCT_ID)
    AND (E.CONTRACT_SITE_ID = CONTRACT_SITE.CONTRACT_SITE_ID)
    AND UPPER(CONTRACT_SITE.SITE_PUB_ID) = N'PTA18DEC200616619')
    AND EP.PARTY_ROLE = 'ENDCO'
    ORDER BY E.ENTITLEMENT_ID
  2. Adriaan New Member

    Performance would probably improve further if you included proper JOINs to the CONTRACT_SITE and PRODUCT tables, instead of joining in the WHERE clause.
    You should keep the filtering in the WHERE clause, and not apply it in the ON part of a JOIN. On the other hand, that is probably the reason for the difference in performance. Still, use the FROM clause for JOINing tables, and the WHERE clause for filtering.
    Another improvement would be to drop the UPPER() function around SITE_PUB_ID, since it is pointless unless it is a column with a case-sensitive collation. If there is an index for SITE_PUB_ID, the use of the UPPER() function actually forces SQL Server to ignore the index.

Share This Page