Large File - Adding Lines - Out Of Memory

Question

I have a very large sql insert file which is throwing "out of memory" errors when running it in SQL Enterprise Manager.

The advice I have seen is to add the "GO" command every X amount of rows to the inserts are "batched".

I am trying to write a small function to read the file and every 50 lines add a row with the text "GO"

The code I have written is also throwing System.OutOfMemoryException when I run it.

Can anyone suggest a better way of writing my code to fix this problem please?

This is what I have written:

public static void AddGo()
    {
        int currentline = 0;

        string FilePath = @"C:\Users\trevo_000\Desktop\fmm89386.sql";

        var text = new StringBuilder();

        foreach (string s in File.ReadAllLines(FilePath))
        {
            // add the current line
            text.AppendLine(s);

            // increase the line counter
            currentline += 1;

            if (currentline == 50)
            {
                text.AppendLine("GO");
                currentline = 0;
            }
        }

        using (var file = new StreamWriter(File.Create(@"C:\Users\trevo_000\Desktop\fmm89386Clean.sql")))
        {
            file.Write(text.ToString());
        }

    }

Use `ReadLines` instead of `ReadAllLines` and do the writing in the same loop you're reading, instead of using a `StringBuilder`. That way you don't need to keep more than one line in memory at a time. — CodesInChaos, Feb 04 '15 at 10:57
What is your goal? Insert a large number of rows? Why a file? Can't you use SqlCommand? Or Bulk import? — DrKoch, Feb 04 '15 at 10:58
Alternatively you could use a 64 bit build of your application on a computer with sufficient RAM and decide that optimizing memory usage for a one-off script is not worth the bother. — CodesInChaos, Feb 04 '15 at 10:58

score 3 · Accepted Answer · edited May 23 '17 at 11:49

You're keeping the file in memory and then writing it from memory to a file. Instead of doing that write the output file as you work through the input file; this sort of thing:

public static void AddGo() {
    int currentline = 0;

    string inputFilePath = @"C:\Users\trevo_000\Desktop\fmm89386.sql";
    string outputFilePath = @"C:\Users\trevo_000\Desktop\fmm89386Clean.sql";
    using (var outputFileStream=File.CreateText(outputFilePath)) {
        foreach (string s in File.ReadLines(inputFilePath))
        {
            // add the current line
            outputFileStream.WriteLine(s);

            // increase the line counter
            currentline += 1;

            if (currentline == 50)
            {
                outputFileStream.WriteLine("GO");
                currentline = 0;
            }
        }
    }
}

Note the use of ReadLines on the input file, rather than ReadAllLines - see What is the difference between File.ReadLines() and File.ReadAllLines()? for more info on that.

Large File - Adding Lines - Out Of Memory

1 Answers1