c# Optimized Select string creation loop

Question

I'm looking to optimized this piece of code. It will process 15000 - 20000 lines. For now I have 9000 lines and it take 30 sec approx. I know string concatenation is slow but I don't know how to do it another way.

                    //
                    // Check if composite primary keys existe in database
                    //

                    string strSelect = "SELECT * FROM " + _strTableName + " WHERE ";
                    for (int i = 0; i < strCompositeKeyField.Length; i++)
                    {
                        bool boolKeyProcess = false;
                        strSelect += _strHeaderLineSplitedArray[(int)arrayListCompositeKeyIndex[i]] + " = ";
                        DataColumn thisColomn = _dsProcessDataFromFileAndPutInDataSetDataSet.Tables["Repartition"].Columns[_strHeaderLineSplitedArray[(int)arrayListCompositeKeyIndex[i]]];
                        //_strProcessDataFromFileAndPutInDataSetLog += "Debug: Composite key : " + _strHeaderLineSplitedArray[(int)arrayListCompositeKeyIndex[i]] + " dataType : " + thisColomn.DataType.ToString() + " arrayListCompositeKeyIndex[i] = " + arrayListCompositeKeyIndex[i] + " \n";
                        // check if field is datetime to make convertion
                        if (thisColomn.DataType.ToString() == "System.DateTime")
                        {
                            DateTime thisDateTime = DateTime.ParseExact(strReadDataLineSplited[(int)arrayListCompositeKeyIndex[i]], _strDateConvertion, null);
                            strSelect += "'" + thisDateTime.ToString() + "'";
                            boolKeyProcess = true;
                        }
                        // check if field a string to add ''
                        else if (thisColomn.DataType.ToString() == "System.String")
                        {
                            strSelect += "'" + strReadDataLineSplited[(int)arrayListCompositeKeyIndex[i]] + "'";
                            boolKeyProcess = true;
                        }
                        // check if field need hour to second converstion
                        else
                        {
                            for (int j = 0; j < strHourToSecondConverstionField.Length; j++)
                            {
                                if (strCompositeKeyField[i] == strHourToSecondConverstionField[j])
                                {
                                    DateTime thisDateTime = DateTime.ParseExact(strReadDataLineSplited[(int)arrayListCompositeKeyIndex[i]], _strHourConvertion, System.Globalization.CultureInfo.CurrentCulture);

                                    strSelect += thisDateTime.TimeOfDay.TotalSeconds.ToString();
                                    boolKeyProcess = true;
                                }
                            }
                        }
                        // if not allready process process as normal
                        if (!boolKeyProcess)
                        {
                            strSelect += strReadDataLineSplited[(int)arrayListCompositeKeyIndex[i]];
                        }
                        // Add " AND " if not last field
                        if (i != strCompositeKeyField.Length - 1)
                        {
                            strSelect += " AND ";
                        }

                    }


                    //_strProcessDataFromFileAndPutInDataSetLog += "Debug: SELECT = " + strSelect + "\n";

                    SqlDataAdapter AdapterCheckCompositePrimaryKeys = new SqlDataAdapter(strSelect, _scProcessDataFrinFileAndPutInDataSetSqlConnection);
                    DataSet DataSetCheckCompositePrimaryKeys = new DataSet();

                    AdapterCheckCompositePrimaryKeys.Fill(DataSetCheckCompositePrimaryKeys, "PrimaryKey");

Answer 1

You should definitely take a look at StringBuilder - it works wonders for scenarios like this one. In this case, i'd use a mix of AppendFormat and Append. I tend to like AppendFormat to make the strings a bit easier to follow.

//
// Check if composite primary keys existe in database
//

StringBuilder strSelect = "SELECT * FROM " + _strTableName + " WHERE ";

for (int i = 0; i < strCompositeKeyField.Length; i++)
{
    bool boolKeyProcess = false;

    strSelect.AppendFormat("{0} =", 
        _strHeaderLineSplitedArray[(int)arrayListCompositeKeyIndex[i]]);

    DataColumn thisColomn = 
        _dsProcessDataFromFileAndPutInDataSetDataSet
        .Tables["Repartition"]
        .Columns[_strHeaderLineSplitedArray[(int)arrayListCompositeKeyIndex[i]]];

    //_strProcessDataFromFileAndPutInDataSetLog += "Debug: Composite key : " + _strHeaderLineSplitedArray[(int)arrayListCompositeKeyIndex[i]] + " dataType : " + thisColomn.DataType.ToString() + " arrayListCompositeKeyIndex[i] = " + arrayListCompositeKeyIndex[i] + " \n";
    // check if field is datetime to make convertion
    if (thisColomn.DataType.ToString() == "System.DateTime")
    {
        DateTime thisDateTime = 
            DateTime.ParseExact(strReadDataLineSplited[(int)arrayListCompositeKeyIndex[i]], 
           _strDateConvertion, null);

        strSelect.AppendFormat("'{0}'", thisDateTime.ToString());
        boolKeyProcess = true;
    }
    // check if field a string to add ''
    else if (thisColomn.DataType.ToString() == "System.String")
    {
        strSelect.AppendFormat("'{0}'", 
            strReadDataLineSplited[(int)arrayListCompositeKeyIndex[i]]);

        boolKeyProcess = true;
    }
    // check if field need hour to second converstion
    else
    {
        for (int j = 0; j < strHourToSecondConverstionField.Length; j++)
        {
            if (strCompositeKeyField[i] == strHourToSecondConverstionField[j])
            {
                DateTime thisDateTime = DateTime.ParseExact(
                    strReadDataLineSplited[(int)arrayListCompositeKeyIndex[i]],
                    _strHourConvertion, 
                    System.Globalization.CultureInfo.CurrentCulture);

                strSelect.Append(thisDateTime.TimeOfDay.TotalSeconds.ToString());
                boolKeyProcess = true;
            }
        }
    }
    // if not allready process process as normal
    if (!boolKeyProcess)
    {
        strSelect.Append(strReadDataLineSplited[(int)arrayListCompositeKeyIndex[i]]);
    }
    // Add " AND " if not last field
    if (i != strCompositeKeyField.Length - 1)
    {
        strSelect.Append(" AND ");
    }

}


//_strProcessDataFromFileAndPutInDataSetLog += "Debug: SELECT = " + strSelect + "\n";

SqlDataAdapter AdapterCheckCompositePrimaryKeys = new SqlDataAdapter(strSelect.ToString(), _scProcessDataFrinFileAndPutInDataSetSqlConnection);
DataSet DataSetCheckCompositePrimaryKeys = new DataSet();

AdapterCheckCompositePrimaryKeys.Fill(DataSetCheckCompositePrimaryKeys, "PrimaryKey");

Answer 2

Use a StringBuilder and its Append() method.

Answer 3

Make use of a StringBuilder rather than string concatenation.

Answer 4

Use StringBuilder for your string manipulation like strSelect += ... instead use stringBuilder.Append("...");

Answer 5

Have u tried the StringBuilder object ? http://msdn.microsoft.com/en-us/library/system.text.stringbuilder.aspx

Answer 6

To answer your direct question you'll almost certainly benefit from using StringBuilder to build the string up, then ToString() it at the end.

However, if you could give us an overview of the intention (so we don't have to wade through to deduce it) we can probably recommend a better way.

Answer 7

After a quick look, one thing that stands out is that you should be using the StringBuilder class to build up the string, instead of continually concatenating on to your strSelect string variable. Excerpt from the linked MSDN article:

The performance of a concatenation operation for a String or StringBuilder object depends on how often a memory allocation occurs. A String concatenation operation always allocates memory, whereas a StringBuilder concatenation operation only allocates memory if the StringBuilder object buffer is too small to accommodate the new data. Consequently, the String class is preferable for a concatenation operation if a fixed number of String objects are concatenated. In that case, the individual concatenation operations might even be combined into a single operation by the compiler. A StringBuilder object is preferable for a concatenation operation if an arbitrary number of strings are concatenated; for example, if a loop concatenates a random number of strings of user input.

Answer 8

I'm a database guy, so hopefully I don't sound like an idiot here, but can you use the StringBuilder class? I don't know if that requires the .NET framework or not.

Answer 9

You've received a lot of good suggestions to use StringBuilder. To improve your performance more and since you are expecting a large string result, I would recommend as well, that you choose a good initial capacity to reduce the number of times the internal string buffer needs to be expanded.

StringBuilder strSelect = new StringBuilder("SELECT * FROM " + _strTableName + " WHERE ", 8192);

Note that I picked 8192 characters here, but you may want to initialize this to a larger number if you really are adding "9000" lines of data to your condition. Find out your typical size and set it the capacity to just a small amount above that.

The way StringBuilder works is that as you append to the string and reach the current capacity, it must create a new buffer and copy the contents of the old buffer to the new one then append the new characters. To optimize performance, the new buffer will be double the old size. Now, the default initial capacity is either 16 characters or the size of the initializing string. If your resulting string is 5000 characters long, then a StringBuilder created with a default size will have to be expanded 9 times - that requires new memory allocation and copying all the previous characters! If, however, you knew this would happen regularly and created the StringBuilder with the right capacity, there would be no additional allocation or copies.

Normally you should not need to worry about internal operations of an object, but you do sometimes for performance such as this. Since StringBuilder does provide a way for you to specific a recommended initial capacity, take advantage of that. You do not know if the doubly/copy algorithm will change in the future nor the initial default capacity may change, but your specification of an initial capacity will continue to allocate the right sized builder - because this is part of the public contract.