Hi have a below simple query, the second query after union I have written to filter out few duplicate records
e.g the first query was giving me 150 and the second with out and r.ruleid = ... filter was giving 12 records here in the second query the d.Main_Query was getting duplicated 2times, which I do not want. hence now with the below filtering condition etc in the second query I am getting what exactly 6 rec which is correct.
Here my question is can I re-write this query in better way for performance ? as I see that same predicate is getting repeated in the second query for r.ruleid..filtering condition.
SELECT DISTINCT
r.[RuleID],
d.Rule_ID,
d.Sub_Rule_ID,
d.Execute_Order,
d.Main_Query,
d.Table_Name,
d.Record_Limit
FROM rule_Detail d
INNER JOIN jp_bcc_Rule r ON d.Rule_ID = r.RuleKey
WHERE d.[Include] = 'Yes'
and d.Include_Platform_Column='true'
UNION
SELECT DISTINCT
r.[RuleID],
d.Rule_ID,
d.Sub_Rule_ID,
d.Execute_Order,
d.Main_Query,
d.Table_Name,
d.Record_Limit
FROM rule_Detail d
INNER JOIN jp_bcc_Rule r ON d.Rule_ID = r.RuleKey
WHERE d.[Include] = 'Yes'
and d.Include_Platform_Column='false'
and r.ruleid = ( select top 1 r.[ruleid] FROM rule_Detail d
INNER JOIN jp_bcc_Rule r ON d.Rule_ID = r.RuleKey
WHERE d.[Include] = 'Yes'
and d.Include_Platform_Column='false'
)
-Neil
query tuning
Posted - 10/01/2012 : 04:10:07
