Performing and timing loops with SQL queries

I am a casual C# programmer, not formally trained in OOP at all; mostly I focus on Transact-SQL in SQL Server, and so when I write C# apps I get grief about the constructs and methods I use. Here is a simple app I wrote for this blog post - it was not meant to be a production app, and performance is certainly not a priority, but I would like to become better at writing optimal code naturally.

The purpose of the code is quite simple - for 100,000 cycles I need to generate a random name and number from a SQL Server stored procedure, then write those values to a table . After that, read 100 random rows from the table, 1,000 times, and not really care what is done as you iterate through the 100 rows.

The point of the exercise was to profile the effects of encryption on the application. So what the reader loop does is not really material and does not need to be optimized, it is just there to take up time and be the same in both cases. Also it should be noted that the multiple connections are intentional - it is supposed to simulate - without getting into multi-threading or multiple instances of the app - all of the overhead you would see in a highly concurrent app (even though, deep down, this still executes serially).

Items that were pointed out to me (and that I'd like to better understand):

• for is better than while

• Variable declaration at the top is bad

• Code is bloated

• Legibility is bad

I am also sure there is a better way to time the performance of the code, other than dumping the current clock time to the console.

```
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace AEDemo
{
class Program
{
static void Main(string[] args)
{
using (SqlConnection con1 = new SqlConnection())
{
Console.WriteLine(DateTime.UtcNow.ToString("hh:mm:ss.fffffff"));
string name;
string EmptyString = "";
string conSt

In the case of both if your while loops, you could easily make them for loops instead, which tends to be the best practice.

int i = 1;
    while (i <= 100000)
    {
        // main while code
        i++
    }

Should be rewritten as:

for (int i = 1; i <= 100000; i++)
{
    // main while code
}

Likewise:

i = 1;
    while (i <= 1000)
    {
        // main while code
        i++;
    }

Would become:

for (int i = 1; i <= 1000; i++)
{
    // main while code
}

This allows you to recycle the variable i later, and also prevents from accidentally forgetting to reset i back to 1 between loops. Generally, the variable i stands for iterator, and is typically reserved exclusively for loops.

This has no real performance impact, but it has a severe readability and maintainability impact. Using the for loop, it is immediately clear what is happening to the iterator. Using the while loop, you have to search through the body of the while to find where you manipulate the iterator.

As far as:


variable declaration at the top is bad

Generally it is frowned upon. You should declare variables as close to their usage as possible, and within the tightest block that they are to be used in.

For example, the:

int salary;
   string name;

Should be declared within your first loop (which should now be a for loop):

for (int i = 1; i <= 100000; i++)
{
    int salary;
    string name;
}

This is so that variables cannot unexpectedly be used in places where they should not, and so that they can be recycled when they fall out-of-scope immediately. It also means that you cannot accidentally use the value of them outside of the block they apply to.

Try to choose more effective variable names. Never abbreviate except for certain well-accepted abbreviates (i for iterator, e for eventArgs, etc.):

using (SqlConnection con1 = new SqlConnection())
  {

Would be more accepted as:

using (SqlConnection connection1 = new SqlConnection())
  {

This is mostly a best-practice.

You are likely going to see a huge performance drag using the EmptyString variable as you are: string types are immutable, which means each time you do EmptyString += rdr[0].ToString(); it creates a new instance of a string with the combination, then assigns it to EmptyString, then schedules the original string for garbage collection.

Instead, use a StringBuilder:

StringBuilder emptyStringBuilder = new StringBuilder();

for (int i = 1; i <= 1000; i++)
{
    // ...
    while (rdr.Read())
    {
        emptyStringBuilder.Append(rdr[0].ToString());
    }
    // ...
}

This saves a lot of performance. See MSDN for more information.

Another variable naming nit-pick: you should always camelCase local and private variable names: i.e. emptyString instead of EmptyString.

This is another best-practice.

After all our rewrites:

```
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace AEDemo
{
class Program
{
static void Main(string[] args)
{
using (SqlConnection connection1 = new SqlConnection())
{
Console.WriteLine(DateTime.UtcNow.ToString("hh:mm:ss.fffffff"));
string connectionString = ConfigurationManager.ConnectionStrings[args[0]].ToString();

for (int i = 1; i <= 100000; i++)
{
connection1.ConnectionString = connectionString;

string name;
int salary;

using (SqlCommand command1 = new SqlCommand("dbo.GenerateNameAndSalary", connection1))
{
command1.CommandType = CommandType.StoredProcedure;
SqlParameter nameParameter = new SqlParameter("@Name", SqlDbType.NVarChar, 32) { Direction = ParameterDirection.Output };
SqlParameter salaryParameter = new SqlParameter("@Salary", SqlDbType.Int) { Direction = ParameterDirection.Output };

command1.Parameters.Add(nameParameter);
command1.Parameters.Add(salaryParameter);

connection1.Open();
command1.ExecuteNonQuery();
name = nameParameter.Value.ToString();
salary = Convert.ToInt32(salaryParameter.Value);
connection1.Close();
}

using (SqlConnection connection2 = new SqlConnection())
{
connection2.ConnectionString = connectionString;
using (SqlCommand command2 = new SqlCommand("dbo.AddPerson", connection2))
{
command2.CommandType = CommandType.StoredProcedure;
SqlParameter nameParamet

StackExchange Code Review Q#100694, answer score: 31