find record in 10 million rows

Question

I have a text file in comma separated format. Each row has two columns and each column has integer values. Like

12334,23433
23234,45663
234422,324545
324543,23433
143233,23433
.....

The values in second columns are repeated. What I need to accomplish is that find all the values in first columns for which second column has same value and represent them in a row. Like for above data:

23433 12334,324543,143233
45663 23234
324545 234422

What I have done is following:

1. Imported text file into SQL Server table using comma (,) as delimiter.
2. Read text file from code per line.
3. Split line on base of comma(,) and used second column value to send query to SQL Table.
4. Store result in dictionary data structure where key is second column and concatenate all the results form first column value.
5. After all processing, traverse dictionary and write it in a file.

This is definitely taking too much time. I have written code in C#. Any solution in T-SQL will work as well.

Any help to optimize it.

Answer 1

Doing this in pure C# should be fast and easy, as long as the data is small. No need to pull your CSV file into an SQL database.

As long as the file fits into your RAM, the biggest cost should be IO, and not the processing. With 10 million lines, the file should have about 100 MB and take perhaps a second to process.

var lines = File.ReadLines(inputFilename);

var table = lines.Select(line => line.Split(','));
var groups = table.GroupBy(columns => columns[1]);
var output = groups.Select(g => g.Key + " " + string.Join(",", g.Select(columns=>columns[0])));

File.WriteAllLines(outputFilename, output);

Answer 2

just use order by to process one col2 at at time

select col1, col2 
from table  
order by col2, col1

then just write out the line when you get a new value for col2

Int col2Last = 0;  // assume 0 is not a valid value
StringBuilder sb = new string builder();

    while (rdr.read());
    {
        col1 = rdr.GetInt(0);
        col2 = rdr.GetInt(1);
        if(col2 != col2Last and col2Last !=0)
        {
             Console.WriteLine(col2Last.ToString() + " " + sb.ToString());
             sb.clear();
        }
        if (sb.Lenght > 0) sb.Append(",");
        sb.Append(col1.ToString());
        col2Last = col2;
    }
    Console.WriteLine(col2Last.ToString() + " " + sb.ToString());

Answer 3

If you have imported data into table yet, why not to try this way:

declare @t table(c1 int, c2 int)
insert into @t values
(12334,23433),
(23234,45663),
(234422,324545),
(324543,23433),
(143233,23433)

select c2, replace((select cast(c1 as varchar) as 'data()' from @t where c2=t.c2 for xml path('')),' ',', ')
from 
@t t
group by c2