StringBuilder performance in small number of appending

Question

 public DataTable dt_date(resbal obj)
{
    connection();

    string details = @"SELECT donor_contacts.name AS NAME, m_reserve_userdetails.res_no AS 'RESERVATION NO',
    m_room_user.build AS 'BUILDING',m_room.roomno AS 'ROOM NO',DATE_FORMAT(intime,'%r') AS 'CHECK-IN TIME',
    CASE WHEN m_room_user.rent =0 THEN 'Free'  ELSE 'Pass' END AS 'Passtype' FROM  m_reserve_userdetails,donor_contacts,m_room_user,m_room
    WHERE m_reserve_userdetails.id = donor_contacts.id 
    AND  m_room_user.donor_id = m_reserve_userdetails.user_id    
    AND m_reserve_userdetails.status=1 
    AND indate ='" + obj.date + "'";


    OdbcCommand cmd = new OdbcCommand(details, con);
    OdbcDataAdapter oda = new OdbcDataAdapter(cmd);
    DataTable dt = new DataTable();
    oda.Fill(dt);
    return dt;
}

OR

public DataTable dt_date(resbal obj) { connection();

StringBuilder datails = new StringBuilder();
datails.Append("SELECT donor_contacts.name AS NAME, ");
datails.Append("m_reserve_userdetails.res_no AS 'RESERVATION NO',");
datails.Append("m_room_user.build AS 'BUILDING',");
datails.Append("m_room.roomno AS 'ROOM NO',");
datails.Append("DATE_FORMAT(intime,'%r') AS 'CHECK-IN TIME',");
datails.Append("CASE WHEN m_room_user.rent =0 THEN 'Free'  ");
datails.Append("ELSE 'Pass' END AS 'Passtype' FROM  m_reserve_userdetails,");
datails.Append("donor_contacts,m_room_user,m_room");
datails.Append("WHERE m_reserve_userdetails.id = donor_contacts.id");
datails.Append("AND  m_room_user.donor_id = m_reserve_userdetails.user_id");
datails.Append("AND m_reserve_userdetails.status=1");
datails.Append("AND indate ='" + obj.date + "'");

OdbcCommand cmd = new OdbcCommand(datails.ToString(), con);
OdbcDataAdapter oda = new OdbcDataAdapter(cmd);
DataTable dt = new DataTable();
oda.Fill(dt);
return dt;

}

I wish to know, is stringBuilder works better than string even if there is only 8 or 10 appending? I tried a query on this as i added above. But i found no remarkable difference. This query takes time. Most probably it needs a query optimization, i know. But now, i need to know is there any little advance in using stringBuilder while there is only 8 to 10 appending. Thanks in advance.

You are doing a _database call_ direclty after it. The query execution itself will _always_ be slower than any sort of string concatenation. If you want to understand how the StringBuilder works, see [When to use StringBuilder?](http://stackoverflow.com/questions/1825781/when-to-use-stringbuilder) for example. — CodeCaster, Oct 04 '13 at 10:38
Thanks for all answers and your patience. It helps me.Thank you. — sijovw, Oct 04 '13 at 10:52

usr · Answer 1 · 2013-10-04T10:54:21.060

2

When the number of strings to concat is fixed at compile time, normal concatenation always performs better. Also, in your first example you have far less strings to concat.

I don't know of any argument that would be in favor of the StringBuilder here. Very clear case.

Note that the first string literal in the first code snippet is just one multi-line literal. No concatenation here anyway. Also, the compiler optimizes "a" + "b" into "ab".

edited Oct 04 '13 at 10:54

answered Oct 04 '13 at 10:35

usr

168,620
35
240
369

+1 Didn't know that if the number of strings to concat is fixed, it's better. Although probably still micro-optimization. Do you have a source on that? The reason behind it being faster? – Peter Oct 04 '13 at 10:42
Really? Did you notice the usage of this specific concatenation? – walther Oct 04 '13 at 10:43
There is no IL instruction for concatenation of strings. It is a library call to `string.Concat`. Look at the overloads. The compiler always picks the optimal one. – usr Oct 04 '13 at 10:52
@walther was is the "specific" type of usage here? There is a constant number of strings being concatenated. In fact in the 2nd proposal the last Append call has the same concatenation pattern as argument. It makes no sense at all. – usr Oct 04 '13 at 10:52
I guess you meant "what is the...". Look at the code and see how does he want to use the concatenation - SQL QUERY. Unless it's his private test application that will never see real users, I'd never encourage ANY kind of concatenation. He was asking about his specific situation, so he could decide what to do.. – walther Oct 04 '13 at 11:02

Soner Gönül · Answer 2 · 2013-10-04T10:49:08.860

In my opinion, there is no performance issue for this case.

String is immutable, StringBuilder is mutable. If you want to a lot string concatenation, StringBuilder is of course better.

But I see only 12 concatenation here, I don't think they effect huge performance difference.

Take a look at Jeff's article called The Sad Tragedy of Micro-Optimization Theater

EDIT: Yeap, I totally missed abour non-parameterized queries.

You should always use parameterized queries instead this kind of string concatenations. Because these are open for SQL Injection attacks.

score 2 · Answer 3 · answered Oct 04 '13 at 10:43

Neither. You should use a parameterised query. That is the best practice to protect yourself against SQL injection attacks.

If you still want to concatenate the value into a string, there is no reason to use a StringBuilder. Concatenating a known number of strings works even better without the StringBuilder, because literal strings will actually be concatenated by the compiler, not at runtime.

I noticed that you didn't even use the StringBuilder to put the value into a string, but first concatenate the value into a string, then send it to the StringBuilder. If you would use the StringBuilder to put the value in the string, then the value would be in an Append call by itself.

score 1 · Accepted Answer · answered Oct 04 '13 at 10:39

1

From the point of performance, this:

private const string details = "SELECT ... AND indate = '{0}'";

string.Format(details, obj.date);

is definitely better in your case, than StringBuilder or even string concatenations (but for formatting your string needs a little modification, and also you'll need a microscope to see the difference).

From the point of common sense, string concatenations for SQL queries is a way to hell. Use parametrized queries:

private const string details = "SELECT ... AND indate = @indate";

answered Oct 04 '13 at 10:39

Dennis

37,026
10
82
150

Finally someone who noticed the usage - SQL query :) +1 – walther Oct 04 '13 at 10:41
string.Format is many times slower. It concatenates as well (how else could it build a new string?), but in addition it has to parse the format string. – usr Oct 04 '13 at 13:43
@usr: may be. I'm too lazy to measure micro-optimizations. – Dennis Oct 04 '13 at 13:58

score 0 · Answer 5 · answered Oct 04 '13 at 10:45

The correct answer in your scenario is "neither" I'm afraid... You use concatenation of strings for a SQL command and that is a BAD thing every single time. What you should do instead is to use either procedures or queries with parameters. In neither of these approaches you'll ever have a need for concatenation.

If I ignore the fact you're using it for SQL, there's a rule of thumb I use: if you concatenate strings MULTIPLE times, e.g. in a loop, use StringBuilder. In other cases use simple concatenation.

StringBuilder performance in small number of appending

5 Answers5