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