Search datatable faster

Question

I would want to make the search faster as it takes a long time to retentive the data that i require.. is the a faster method to so this..I have written the code below to to allow me to search through a data table and retrieve data which currently is fully functional. its the nested for loops which i am using and wondering if there is another method of completing the tasks that the foor loops get

 DataTable sqlData = GetConfiguration();

       // var q = sqlData.AsEnumerable().Where(data=> data.Field<String>("slideNo")=="5");

        var w = sqlData.AsEnumerable().Where(data => data.Field<String>("slideNo") == "5")
            .Select(data => data.Field<String>("QuestionStartText")).Distinct();

        List<String> queryResult = new List<String>();
        foreach (var item in w.ToArray<string>())
        {
            if (item != null)
            {
                String queryString = item;
                //queryResult.Clear();
                for (int i = 0; i < excelDataTable.Columns.Count; i++)
                {   
                    for (int k = 2; k < excelDataTable.Rows.Count; k++)
                    {
                       String row = "";
                       bool check = excelDataTable.Rows[k][0].ToString().StartsWith(queryString);
                       if (check)
                       {
                           for (int j = 0; j < excelDataTable.Columns.Count; j++)
                           {
                               string value = excelDataTable.Rows[k][j].ToString()+":";
                               row += value;
                           }
                           if (!queryResult.Contains(row))
                           {
                               queryResult.Add(row);
                           }
                       }
                    }
                }
            }               
        } 

Answer 1

The most obvious problem is when there are large number of strings, string concatenation is inherently slow. Try using a StringBuilder instead, like this:

 DataTable sqlData = GetConfiguration();

       // var q = sqlData.AsEnumerable().Where(data=> data.Field<String>("slideNo")=="5");

        var w = sqlData.AsEnumerable().Where(data => data.Field<String>("slideNo") == "5")
            .Select(data => data.Field<String>("QuestionStartText")).Distinct();

        List<String> queryResult = new List<String>();
        StringBuilder sb = new StringBuilder();
        foreach (var item in w.ToArray<string>())
        {
            if (item != null)
            {
                String queryString = item;
                //queryResult.Clear();
                for (int i = 0; i < excelDataTable.Columns.Count; i++)
                {   
                    for (int k = 2; k < excelDataTable.Rows.Count; k++)
                    {
                       sb.Clear();
                       bool check = excelDataTable.Rows[k][0].ToString().StartsWith(queryString);
                       if (check)
                       {
                           for (int j = 0; j < excelDataTable.Columns.Count; j++)
                           {
                               sb.Append(excelDataTable.Rows[k][j].ToString());
                               sb.Append(':');
                           }
                           string row = sb.ToString();
                           if (!queryResult.Contains(row))
                           {
                               queryResult.Add(row);
                           }
                       }
                    }
                }
            }               
        } 

Answer 2

Guessing at intent a little bit here, but this should simplify and be more performant (I don't see the point in the outer loop over the columns). That being said, where is your bottleneck? I'd almost guess it's in the database operation GetConfiguration() more than any of this code.

    var sqlData = GetConfiguration();

        ////var q = sqlData.AsEnumerable().Where(data => data.Field<string>("slideNo") == "5");
        var w = sqlData
            .AsEnumerable()
            .Where(data => data.Field<string>("slideNo") == "5")
            .Select(data => data.Field<string>("QuestionStartText")).Distinct();
        var queryResult = new List<string>();

        foreach (var queryString in w.Where(item => item != null))
        {
            ////queryResult.Clear();
            for (var k = 2; k < excelDataTable.Rows.Count; k++)
            {
                if (!excelDataTable.Rows[k][0].ToString().StartsWith(queryString))
                {
                    continue;
                }

                var rowSb = new StringBuilder();

                for (var j = 0; j < excelDataTable.Columns.Count; j++)
                {
                    rowSb.Append(excelDataTable.Rows[k][j] + ":");
                }

                var row = rowSb.ToString();

                if (!queryResult.Contains(row))
                {
                    queryResult.Add(row);
                }
            }
        }