Pretty format sql in .NET code, performance?

Question

Hi,

Will the compiler optimize pretty formatted strings, or will that code run slower than strings that are not divided in a readable way?

for example

string sql = 
   "select * " +
   "from person " +
   "where id = :id";

or

string sql = "select * from person where id = :id";

This is just a small example. You know how complicated the sql can get.

Answer 1

Just use:-

string sql = 
   @"select * 
     from person
     where id = :id";

This is from the compilers point of view identical to the single line solution. Although I wouldn't be surprised to see the concatenation of literal strings optimised out by the compiler anyway. However the common gotcha with the concatenation approach is forgetting to include whitespace at the end of string.

Answer 2

You can use

string s = @"SELECT *
FROM person
WHERE id = :id";

Answer 3

String constants are folded at compile time so the two code fragments above are essentially identical.

Whether it's a good idea to have inline SQL string is another matter entirely...

Answer 4

Ah - one of the eternal verities of C#. Which is better, code that concatenates strings using + or code that doesn't? In your case, the answer depends on which version of .NET you're using .NET1 could only optimize the + string in a single statement so far. Too many + in a single string resulted in poorer performance as the compiler had to resort to creating new string instances to cope with additional string parts. From .NET 2, the architecture changed slightly, and multiple + statements are concatenated quite seamlessly by the compiler.

Answer 5

You can test this with a simple program:

Console.WriteLine("a" + "b");

Using reflector, you can easily disassemble the resulting binary. In Release mode, the IL this generates is:

L_0000: ldstr "ab"
L_0005: call void [mscorlib]System.Console::WriteLine(string)

So .NET does optimize "pretty formatted strings".