Writing very large datasets to textfile

I have a very large dataset which I am currently writing out to a text file (IO). It is very s开发者_JAVA百科low and causing the system to chew up a lot of resources as there are 10's of thousands of rows.

I'm wondering if anybody can recommend a good way to do this to reduce the load on my system or at least smooth out the process to avoid big spikes in demand for memory resources etc. I don't mind if it means it takes longer, but as long as it's not putting too much load on the machine.

---

Your question hardly makes sense, but assuming you are reading the results from database in chunks you could write them in chunks to the file to avoid loading the entire dataset in memory, just like so:

using (var conn = new SqlConnection(SomeConnectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT foo, bar FROM baz;";
    using (var reader = cmd.ExecuteReader())
    {
        using (var writer = new StreamWriter("result.txt"))
        {
            while (reader.Read())
            {
                var foo = reader.GetString(reader.GetOrdinal("foo"));
                var bar = reader.GetInt32(reader.GetOrdinal("bar"));
                writer.WriteLine(string.Format("{0}, {1}", foo, bar));
            }
        }
    }
}

In terms of memory consumption this will Rock'N'Roll and in terms of performance it would of course depend on the optimization of your SQL query and the capabilities of your SQL server.

---

If the system does not depend on that, you can spawn a thread to do the actual writing and trying to batch/buffer it in order to minimize cpu/memory spikes. It would depend on your particular case and you do not give much information :)

---

Use a StreamWriter to write the file I recently had to write a 3 million line file and it seemed to work very well. Make sure you are also reading the large amount of data in in a stream.

---

In that case you shouldn't load the whole dataset in memory. Considering I use NHibernate as my ORM, for such cases I would read from DB in small batches like 100 rows at a time per transaction. This way at any given moment my memory will hold only 100 rows of data rather than 100000, write the 100 rows to the file then again read the next 100 rows from database and write to file etc.

Look for paging.

---

One of solution for writing into file is using log4Net to write file.

It's effective and not suck too much resource.