This is my .cs part code where I am calling storeprocedure in LINQ to SQL:

var rr_j_cat = db.allcategories().ToList();//its store procedure calling thousands of rows

if (rr_j_cat.Count() != 0)
   DataTable dt = new DataTable();//making dynamic datatable
   dt.Columns.Add("sub_id");  //dynamic columns
   dt.Columns.Add("sibheadername");  //dynamic columns
   foreach (var it in rr_j_cat) // will rotate thousands time which makes process talking more time 
      DataRow dr = dt.NewRow(); // dyanamic rows
      var rr_sel_cat = db.selcategories(Convert.ToInt32(it.sub_id)).ToList(); //another storeprocedure to check a count of data present in another data or table        
      int count = Convert.ToInt32(rr_sel_cat.First().Column1);
      if (count != 0)
         dr["sub_id"] = it.sub_id;
         dr["sibheadername"] = it.sibheadername + " (" + count + ")"; // adding count besides subheader name which you can see in below image on dance 
         count =0;
           dr["sub_id"] = it.sub_id;
            dr["sibheadername"] = it.sibheadername;
     dd_j_area.DataSource = dt; // binding with dropdownlist
     dd_j_area.DataTextField = "sibheadername";
     dd_j_area.DataValueField = "sub_id";
     dd_j_area.Items.Insert(0, "");

It's working fine, but taking more time.

It will show as


  • 1
    \$\begingroup\$ Can you not have one stored procedure return you the results rather than having to call two? \$\endgroup\$
    – dreza
    Dec 21, 2013 at 9:15
  • \$\begingroup\$ Have you thought about writing it in assembly? Bad and overused joke... sorry. \$\endgroup\$ Dec 22, 2013 at 3:01

3 Answers 3


The principal performance issues are on the database side (or maybe you could do some kind of caching or pre-loading), but your code could be made much clearer and shorter (there should be also some minor performance improvements):

string[] columnNames = new[] { "sub_id", "sibheadername" };

var categories = db.allcategories().ToList(); 

if (categories.Count == 0)

var dt = new DataTable();

foreach (var category in categories)
    var selectedCategories = db.selcategories(Convert.ToInt32(category.sub_id)).ToList();
    int count = Convert.ToInt32(selectedCategories.First().Column1);

    DataRow dr = dt.NewRow();
    dr[0] = category.sub_id;
    dr[1] = count != 0
                ? category.sibheadername + " (" + count + ")" // btw. this is FASTER (in this case) than a StringBuilder!
                : category.sibheadername;


areaDropdown.DataSource = dt;
areaDropdown.DataTextField = columnNames[1];
areaDropdown.DataValueField = columnNames[0];
areaDropdown.Items.Insert(0, "");
  • \$\begingroup\$ dr is missing.. \$\endgroup\$
    – Xtremcool
    Dec 21, 2013 at 8:35
  • \$\begingroup\$ You're right. Simply overseen it. Will edit the answer. \$\endgroup\$ Dec 21, 2013 at 8:39
  • \$\begingroup\$ hey its working faster \$\endgroup\$
    – Xtremcool
    Dec 21, 2013 at 8:45
  • \$\begingroup\$ Nice to hear! You're welcome. \$\endgroup\$ Dec 21, 2013 at 8:53
  • \$\begingroup\$ As i am using storeprocedure is there any necessary of using precompiling?? \$\endgroup\$
    – Xtremcool
    Dec 24, 2013 at 5:40

This is how it should be using StringBuilder:

            System.Text.StringBuilder sibHeaderNameBuilder = new StringBuilder();
        dr["sibheadername"] = sibHeaderNameBuilder.ToString();

Note that using the stringBuilder.Append(int value) method, you don't have to convert use the string representation of count.

  • \$\begingroup\$ its adding one more class of using System.Text; ....and on every loop it call this....i think it will make more load on page of name space and this code?????think so... \$\endgroup\$
    – Xtremcool
    Dec 21, 2013 at 8:16
  • \$\begingroup\$ OK, but you should instantiate the stringBuilder object outside of your loop. Also instead of using string concatenation you can use String.Format(), which will be more faster. \$\endgroup\$ Dec 21, 2013 at 8:31
  • \$\begingroup\$ Umhh, nope. Using StringBuilder is actually slower in this concrete case, because the compiler will optimize the statement " (" + count + ")". \$\endgroup\$ Dec 21, 2013 at 9:35
  • \$\begingroup\$ You are right Thomas about the StringBuilder, but I think StringBuilder is a little bit more efficient (in case of memory management) than using string concatenation. \$\endgroup\$ Dec 21, 2013 at 10:12
  • \$\begingroup\$ Generally speaking yes. Actually a lot more efficient (I've seen cases where this was around 1000 times faster). The only case where direct concatenation is faster is the "x" + <somevar> + "y" scenario. \$\endgroup\$ Dec 21, 2013 at 10:28

You asked two things,

  1. Clearer code
  2. Better performance

I'll try to cover both.

First of all it's unnecessary to convert rr_j_cat and rr_sel_cat to list again! So rr_j and rr_list variables are both useless.

Second, instead of converting rr_sel_cat.First().Column1 to string, which in your case will instantiate a string variable (count) over and over, convert it to and int or if it's already an int or a value type, use it instead and avoid unnecessary boxing which is a performance issue.

Third, instead of string concatenating in dr["sibheadername"] = it.sibheadername + " (" + count + ")";, use StringBuilder class which will be a good performance improvement in your case.

  • \$\begingroup\$ how to make use stringbuilder in this case?..please will u give some demo based on my question?? \$\endgroup\$
    – Xtremcool
    Dec 21, 2013 at 7:47
  • \$\begingroup\$ as you suggested other things are being changed....but string builder is good to use ? \$\endgroup\$
    – Xtremcool
    Dec 21, 2013 at 8:25
  • \$\begingroup\$ Actually StringBuilder is more efficient in case of using memory, but the fastest way will be using string.Format(). \$\endgroup\$ Dec 21, 2013 at 8:38
  • \$\begingroup\$ Umhh, nope. First, the ToList() prevents the stored procedure from being executed more than once. Second, using StringBuilder is actually slower in this concrete case, because the compiler will optimize the statement " (" + count + ")". \$\endgroup\$ Dec 21, 2013 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.