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

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?

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
– Jack Marchetti, Commented Feb 4, 2013 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
– GarethD, Commented Feb 4, 2013 at 21:25
@GarethD -- not necessarily a minor point, especially if FieldCount is more than 10 or so! — Bob Kaufman
– Bob Kaufman, Commented Feb 5, 2013 at 3:43

Daniel Kelley · Accepted Answer · 2013-02-04 21:29:10Z

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 4, 2013 at 21:29

Daniel Kelley

7,7677 gold badges45 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adi Over a year ago

definitely, using a StringBuilder is more efficient than concatenating strings. no useless memory allocations, not memory fragmentation in excess, good point (voteup from me)

Conrad Frix Over a year ago

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()

wmaynard Over a year ago

You are a beautiful human being, Daniel. Thank you! Using the StreamWriter code you provided, the execution time is under 40s.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related