Improving processing speed of a large SQL table in C#

Question

I have C# code that does this:

// This returns 10,000 rows quite quickly, but displaying them below is slow
DataTable dt = GetUsageStats();

string html = "<table>";

// This part is really slow
foreach (DataRow dr in dt.Rows)
{
   html += "<tr>";
   html += "<td>" + dr["column1"].ToString() + "</td>";
   html += "<td>" + dr["column2"].ToString() + "</td>";
   ...
   html += "<td>" + dr["column5"].ToString() + "</td>";
   html += "</tr>"; 
}

html += "</table>";

The foreach goes so slow because of the 10,000 or so records. Is there any mechanism to speed this process up?

Thanks!

use [StringBuilder](http://stackoverflow.com/questions/3069416/difference-between-string-and-stringbuilder-in-c-sharp) — Manish Mishra, Sep 06 '13 at 19:48
Concatenating strings in a loop like this is an **O(N^2)** operation because of the progressive reallocation of memory. `StringBuilder` uses a list to hold pointers to the source strings, until you want the output/result string, then it allocates all of the space at once making it an **O(N)** operation. — RBarryYoung, Sep 06 '13 at 20:01
Just a side note: What if one of your data contained for example `
` ? — I4V, Sep 06 '13 at 20:01
Adding this as a comment as it doesn't directly answer your question but, you really don't want to do any of this, sure with stringbuilder the server side will go fast, but on most browsers & end devices displaying 10 000 lines in a table is going to be very slow, even past page load. I strongly suggest you don't limit yourself to 10K lines but instead just load 100 & load more asynchronously with ajax as your end user scroll (or, if you want to display it all at once, switch away from a web app to a WPF app that will display millions of items instantly thanks to virtualisation) — Ronan Thibaudau, Sep 09 '13 at 00:01
Offtopic a little: depending on what it is and where it came from you might want to HtmlEncode the data. — Phil Haselden, Apr 15 '14 at 23:16

JeremiahDotNet · Accepted Answer · 2013-09-06T19:55:18.247

5

You want to use StringBuilder.

var sb = new StringBuilder();
for(int i=0; i<10000; i++)
{
    sb.Append("somestring");
}
string myString = sb.ToString();

Strings are immutable. Every time you concatenate two strings together the entire result must be written to memory. StringBuilder provides a mutable way to work with strings and provides a large performance increase in some cases.

edited Sep 06 '13 at 19:55

answered Sep 06 '13 at 19:49

JeremiahDotNet

910
4
9

Good solution, it would be useful to explain why – jdphenix Sep 06 '13 at 19:50
1

@jdphenix Thanks. I added a brief explanation. – JeremiahDotNet Sep 06 '13 at 19:56

score 3 · Answer 2 · answered Sep 06 '13 at 19:49

StringBuilder class to the rescue

StringBuilder bb = new StringBuilder();

bb.Append("string1");
bb.Append("string2");
bb.Append("string3");

string result = bb.ToString();

StringBuilder() is designed to be modified. Strings are immutable, so they are recreated from scatch each time you modify them.

Improving processing speed of a large SQL table in C#

2 Answers2