Slow, Large SQL Query from C# / ASP.NET

Question

Possible Duplicate:
How to efficiently write to file from SQL datareader in c#?

I am currently trying to create a web application that uses read-only access to allow users to download large files from our database. The table in question has 400,000 records in it and generates a 50 MB .csv file when exported.

It takes about 7s to run the statement "SELECT * FROM [table]" on SQL server, and about 33s to do so from my web application (hosted on a different server). This is reading all the data into a System.Data.SqlClient.SqlDataReader object.

My problem is that I am at a loss for converting my SqlDataReader to a .csv file. Converting each row of the SqlDataReader to a string and outputting that string to a file line by line takes almost 2 hours, which is unacceptable. Below is the code I'm using to create a file on the web application's server:

    while (rdr.Read())
    {
        string lineout = "";
        for (int index = 0; index < rdr.FieldCount; index++)
            lineout += rdr[index].ToString().Replace(',', ' ') + ',';
        write(lineout, filename); //uses StreamWriter.WriteLine()
    }

There has to be a better way. I've looked around and saw a lot of suggestions that essentially recommend doing the above to create a file. This works great with smaller tables, but not the two really large ones we use every day. Can anyone give me a push in the right direction?

http://stackoverflow.com/questions/9055521/how-to-efficiently-write-to-file-from-sql-datareader-in-c — samuelesque, Feb 04 '13 at 21:06
Assuming you're running on this on a server? How does the performance of the machine react when you run the query? You're not using Datasets anywhere are you? — Jack Marchetti, Feb 04 '13 at 21:21
It's a minor point, but it would be better to use StringBuilder to concatenate the strings, rather than `lineout += rdr[index]...` since a string instance is immutable each loop creates a new string if you use +=, whereas stringbuilder is designed for operations like this and will be a lot less stressful on the memory allocator. — GarethD, Feb 04 '13 at 21:25
@GarethD -- not necessarily a minor point, especially if FieldCount is more than 10 or so! — Bob Kaufman, Feb 05 '13 at 03:43

score 5 · Accepted Answer · answered Feb 04 '13 at 21:29

5

You could try building your lineout with a StringBuilder rather than manually concatenating strings:

//you can test whether it makes any difference in performance declaring a single
//StringBuilder and clearing, or creating a new one per loop
var sb = new StringBuilder();

while (rdr.Read())
{
    for (int index = 0; index < rdr.FieldCount; index++)
        sb.Append(rdr[index].ToString().Replace(',', ' ').Append(',');

    write(sb.ToString(), filename); //uses StreamWriter.WriteLine()
    sb.Clear();
}

Alternatively try to just write to the file directly and avoid generating each line in memory first:

//assume a StreamWriter instance has been created called sw...
while (rdr.Read())
{
    for (int index = 0; index < rdr.FieldCount; index++)
    {
        sw.Write(rdr[index].ToString().Replace(',', ' ');
        sw.WriteLine(",");
    }
}

//flush and close stream

answered Feb 04 '13 at 21:29

Daniel Kelley

7,191
5
39
48

definitely, using a StringBuilder is more efficient than concatenating strings. no useless memory allocations, not memory fragmentation in excess, good point (voteup from me) – Adi Feb 04 '13 at 21:34
The second approach is what I use. Although I do this to get it quoted delimited and comma separated. `sw.Write("\"{0}\"{1}", rdr[index], index == rdr.FieldCount - 1 ? "" : ",");` Then I don't have to worry about commas in the values and in my case I already know there's no `"` in the strings and I also don't have to call ToString() – Conrad Frix Feb 04 '13 at 22:56
1

You are a beautiful human being, Daniel. Thank you! Using the StreamWriter code you provided, the execution time is under 40s. – wmaynard Feb 05 '13 at 00:27

Slow, Large SQL Query from C# / ASP.NET

1 Answers1