• ananda.murugesan (7/14/2013)


    thanks for reply... I got the result 00:00:00 seconds after update stats in NC Clustred Index in RESULT Table..

    Can you post the actual plan?

    Joins in the WHERE clause are not recommended, use ANSI joins instead. Your query can be rewritten like so:

    SELECT

    s.SAMPLED_DATE ,

    s.SAMPLE_NUMBER ,

    sp.[DESCRIPTION] as SAMPLING_POINT ,

    r.NAME as COMPONENT ,

    t.TAG_NAME as IP21_TAG ,

    r.NUMERIC_ENTRY as APPROVED_RESULT ,

    u.DISPLAY_STRING as UOM

    FROM [SAMPLE] s

    INNER JOIN RESULT r

    ON r.SAMPLE_NUMBER = s.SAMPLE_NUMBER

    INNER JOIN OPC_TAG t

    ON t.ANALYSIS = r.ANALYSIS

    AND t.SAMPLING_POINT = s.SAMPLING_POINT

    INNER JOIN SAMPLING_POINT sp

    ON sp.NAME = t.SAMPLING_POINT

    INNER JOIN UNITS u

    ON u.UNIT_CODE = r.UNITS

    WHERE t.OPC_SERVER = @0

    AND s.SAMPLED_DATE > = DATEADD ( MM , @1 , GETDATE ( ) )

    AND s.STATUS = @2

    AND r.STATUS = @3

    AND s.ALIQUOT_GROUP IS NULL

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden