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
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