I Need To Parse 250 Files, Totalling At 1gb Of Data And Upload It To A Sql Server. Can I Get More Efficient Than This?

My current method takes ~40 minutes to parse all of this data : Current logic is: foreach (var file in files){ using (var input = new StreamReader(file.FullName)) { wh

Solution 1:

Try something like this. Experiment with the maxParallelism, start with the number of cores in your system:

classProgram
{
    staticvoidMain(string[] args)
    {
        var maxParallelism = Environment.ProcessorCount;
        Parallel.ForEach(files, new ParallelOptions { MaxDegreeOfParallelism = maxParallelism }, ParseAndPersist);
    }

    publicstaticvoidParseAndPersist(FileInfo fileInfo)
    {
        //Load entire file//Parse file//Execute SQL asynchronously..the goal being to achieve maximum file throughput aside from any SQL execution latency

    }
}

Solution 2:

Going by what you said, each file roughly about 4MB which is not too big to read the whole file into memory and perform the parsing once/per line if you have to navigate through the string buffer in memory. You can also leverage Parallel tasks to process multiple files in parallel - taking advantage of your multicores processor.

Solution 3:

you could try parsing the files in parallel rather than sequentially. You could also try only submitting the sql after parsing all files.

Whether these make any difference its hard to say, as you don't give much information about what your sql submit is doing, but I'd have thought that processing the files in parallel would definitely be beneficial.

Solution 4:

Most likely, your bottleneck is actually the SQL queries/inserts. Are you sure the problem is parsing the file[s]? If it's SQL, I would suggest caching what you have and then doing a bulk data copy.