SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query tuning
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aakcse
Aged Yak Warrior

India
568 Posts

Posted - 10/01/2012 :  04:10:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/01/2012 :  07:03:45  Show Profile  Reply with Quote
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

India
568 Posts

Posted - 10/01/2012 :  10:45:29  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000