Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query tuning

Author  Topic 

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-10-01 : 04:10:07
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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-01 : 07:03:45
If you have the duplicate problem in the second part of the query, don't you/won't you run into that same problem in the first part? In any case, if you just want to modify the second part, the following may be a little bit better from a performance perspective.

SELECT
r.[RuleID],
d.Rule_ID,
d.Sub_Rule_ID,
d.Execute_Order,
d.Main_Query,
d.Table_Name,
d.Record_Limit
FROM
jp_bcc_Rule r
CROSS APPLY
(
SELECT TOP (1) *
FROM rule_Detail d
WHERE
d.Rule_ID = r.RuleKey
AND d.[Include] = 'Yes'
AND d.Include_Platform_Column = 'false'
ORDER BY
d.RuleKey --- change if you need to
) d
If you do want to avoid potential duplicates in the first part, simply remove the where clause on d.Include_Platform_Column='false' (and include Include_Platform_Column column in the result set if you need to see that column).
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-10-01 : 10:45:29
duplicate is only when "d.Include_Platform_Column='false'" for first query this value is ='True' hence no duplicate

-Neil
Go to Top of Page
   

- Advertisement -