June 6, 2008 at 1:17 am
HI,
I have a table with a set of Datas ,I need to select only the Distinct Data in the Value column having count(value)=1.
declare @t table (No int, value int)
insert into @t
select 1,25 union
select 2,25 union
select 1,45 union
select 3,25 union
select 1,35 union
select 2,35 union
select 3,35
select * from @T
output
1 45
I got the output but seems to be less performance because of correlated subquerying.
select * from @t
where value =(select value from @t
group by value
having count(value )=1)
Is there any other way, to do this easily
Will the inner join or the crossApply help in this query to improve performance.
Rajesh
June 6, 2008 at 7:05 am
This should work better than correlated subquery:
declare @t table (No int, value int)
insert into @t
select 1,25 union
select 2,25 union
select 1,45 union
select 3,25 union
select 1,35 union
select 2,35 union
select 3,35
select tt.*
from @t tt
join (select value, count(*) as occurrences
from @t
group by value
having count(*) = 1) as Q on Q.value=tt.value
June 6, 2008 at 7:47 am
Hi Rajesh,
try with this query:
[font="Courier New"]Select
T.*
From
@t AS T
Join
(
Select
Value
From
@t
Group By
Value
Having
Count(*) = 1
) AS X ON X.Value=T.Value[/font]
Bye
Sergio
June 7, 2008 at 7:16 pm
Well the two previous responses are actually posting the same query, which would have been obvious if not for the unusal formatting of the second one.
Two points:
1) The claims that correlated JOINS are faster than correlated SubSelects in SQL2005 is a myth. They do the same thing and, properly optimized, they should take the same time. Actually, SubSelects do slightly less so the can be slighty faster than Joins. There may have been optimizer bugs in SQL2000 that made SubSelects slower than they should be, but those have almost entirely been fixed in SQL2005. The one exception is the aberrant case of multiple redundant SubSelects that could (or should) have been combined into a single SubSelect or JOIN. Since that is not the case here, changing the SubSelects to JOINS will, at best, keep the performance the same or make it worse.
2) The posted "improved" queries with JOINS instead of SubSelects are almost twice as slow.
What will perform better is the following query:
select * from @t
where value IN(select value from @t
group by value
having count(value )=1)
This is the fastest one that I can find, however, I am pretty sure that is only because you are using a Table Variable with no keys or indexes (so every access is a Table Scan). On a Temporary Table, or a normal Table with keys or indexes, I am pretty sure that this will be the fastest:
select Max(No) as No, value
from @t
group by value
having count(value )=1
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2008 at 11:38 pm
Thanks all,
Thanks for everyOne in sharing the Ideas.
RBarryYoung has given the Really superb Answer which is what i am looking For thats keeps away from correlated queries.Once again thanks for your Valuable Support.
Rajesh
June 9, 2008 at 12:40 am
rbarryyoung (6/7/2008)
The claims that correlated JOINS are faster than correlated SubSelects in SQL2005 is a myth. They do the same thing and, properly optimized, they should take the same time. Actually, SubSelects do slightly less so the can be slighty faster than Joins.
Now that's interesting... I'm still more "at home" with SQLS 2000 and I didn't know about this. Will have to test it sometimes (no time for it right now), since we are already running 2005 on most servers.
Thanks for the info, Barry!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply