March 24, 2011 at 3:13 am
I need to get distinct records from a table1 and table2.
Table1 has col1,col2,col3,col4 has contrycode.
in the sp has a input value which contains country code.
this may exits any one of the columns (col1,col2,col3,col4)
the table1 contains 10,00,000 lac records.
I have 3 solution. i am thinking of impact of performance. so which is best one or is any other solution ?
solution 1 ( using OR )
------------------------
Select distinct t.Col5,col6 from table1 t1 Inner join table2 t2
On t1.col6=t2.col2
where col1=@Input
OR col2=@Input
OR col3=@Input
OR col4=@Input
solution 2 (using Union)
------------------------
Select distinct t.Col5,col6 from table1 t1 Inner join table2 t2
On t1.col6=t2.col2
where col1=@Input
Union
Select distinct t.Col5,col6 from table1 t1 Inner join table2 t2
On t1.col6=t2.col2
where col2=@Input
Union
Select distinct t.Col5,col6 from table1 t1 Inner join table2 t2
On t1.col6=t2.col2
where col3=@Input
Union
Select distinct t.Col5,col6 from table1 t1 Inner join table2 t2
On t1.col6=t2.col2
where col4=@Input
Solution 3
-----------
Select distinct t.Col5,col6 into tmptable1 from table1
alter tmptable1 add iscode bit
updated t1 set iscode =1 FROM tmptable1 t1
join table2 t2
On t1.col6=t2.col2 where t1.col1=@Input and t1.isCode =0
updated t1 set iscode =1 FROM tmptable1 t1
join table2 t2
On t1.col6=t2.col2 where t1.col2=@Input and t1.isCode =0
updated t1 set iscode =1 FROM tmptable1 t1
join table2 t2
On t1.col6=t2.col2 where t1.col3=@Input and t1.isCode =0
updated t1 set iscode =1 FROM tmptable1 t1
join table2 t2
On t1.col6=t2.col2 where t1.col4=@Input and t1.isCode =0
Select distinct t.Col5,col6 from table1 t1 Inner join table2 t2
On t1.col6=t2.col2
where iscode =1
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy