Second Time Slow Code
-
Hello I am using Sql Server 2000 Developer Edition, Now I know there are limitations too how many connections you have BUT This piece of code below executes a whole lot slower the second time you run it.
switch(cbSearch.SelectedItem.ToString()) { case "Lastname": cmd = new SqlCommand("SPSearchRmiInfoLastname", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Lastname", txtSearch.Text); break; case "Firstname": cmd = new SqlCommand("SPSearchRmiInfoFirstname", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Firstname", txtSearch.Text); break; } conn.Open(); Search.Clear(); SqlDataReader drd = cmd.ExecuteReader(); while(drd.Read()) { Search.Rows.Add(new object\[\] {drd\[0\], drd\[1\], drd\[2\], drd\[3\]}); sbp.Text = Search.Rows.Count+" records found."; } dg.DataSource = Search; drd.Close(); conn.Close();The results are staggering. First time: 1783 Milliseconds Second time: 150326 Milliseconds HOLY SHAZBOT!! Now that's a difference right there, Any ideas on why this is happening and how I can change it Thanks, Obe NOTE: I already posted this in the Sql/ADO/ADO.NET Section but it has todo with C# as well ------------------ I'm naked under my clothes...
-
Hello I am using Sql Server 2000 Developer Edition, Now I know there are limitations too how many connections you have BUT This piece of code below executes a whole lot slower the second time you run it.
switch(cbSearch.SelectedItem.ToString()) { case "Lastname": cmd = new SqlCommand("SPSearchRmiInfoLastname", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Lastname", txtSearch.Text); break; case "Firstname": cmd = new SqlCommand("SPSearchRmiInfoFirstname", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Firstname", txtSearch.Text); break; } conn.Open(); Search.Clear(); SqlDataReader drd = cmd.ExecuteReader(); while(drd.Read()) { Search.Rows.Add(new object\[\] {drd\[0\], drd\[1\], drd\[2\], drd\[3\]}); sbp.Text = Search.Rows.Count+" records found."; } dg.DataSource = Search; drd.Close(); conn.Close();The results are staggering. First time: 1783 Milliseconds Second time: 150326 Milliseconds HOLY SHAZBOT!! Now that's a difference right there, Any ideas on why this is happening and how I can change it Thanks, Obe NOTE: I already posted this in the Sql/ADO/ADO.NET Section but it has todo with C# as well ------------------ I'm naked under my clothes...
I can see that you have 2 different stored procedures. Is it always the same stored proc that is called the second time? Also, as a piece of advice (not related to your problem), don't start you stored procedures name with "sp", because the server search for it in the "master" database when they begin with "sp". -------- "I say no to drugs, but they don't listen." - Marilyn Manson
-
I can see that you have 2 different stored procedures. Is it always the same stored proc that is called the second time? Also, as a piece of advice (not related to your problem), don't start you stored procedures name with "sp", because the server search for it in the "master" database when they begin with "sp". -------- "I say no to drugs, but they don't listen." - Marilyn Manson
-
Hello I am using Sql Server 2000 Developer Edition, Now I know there are limitations too how many connections you have BUT This piece of code below executes a whole lot slower the second time you run it.
switch(cbSearch.SelectedItem.ToString()) { case "Lastname": cmd = new SqlCommand("SPSearchRmiInfoLastname", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Lastname", txtSearch.Text); break; case "Firstname": cmd = new SqlCommand("SPSearchRmiInfoFirstname", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Firstname", txtSearch.Text); break; } conn.Open(); Search.Clear(); SqlDataReader drd = cmd.ExecuteReader(); while(drd.Read()) { Search.Rows.Add(new object\[\] {drd\[0\], drd\[1\], drd\[2\], drd\[3\]}); sbp.Text = Search.Rows.Count+" records found."; } dg.DataSource = Search; drd.Close(); conn.Close();The results are staggering. First time: 1783 Milliseconds Second time: 150326 Milliseconds HOLY SHAZBOT!! Now that's a difference right there, Any ideas on why this is happening and how I can change it Thanks, Obe NOTE: I already posted this in the Sql/ADO/ADO.NET Section but it has todo with C# as well ------------------ I'm naked under my clothes...
Another quick performance tip (although it doesn't explain why it is slower the second time around) Change your while loop to:
while(drd.Read())
{
Search.Rows.Add(new object[] {drd[0], drd[1], drd[2], drd[3]});
}
sbp.Text = string.Concat(Search.Rows.Count.ToString()," records found.")Now you only generate your text once, which should speed things up. Also, I changes the stringA + stringB notation in to a string.Concat() method call which is a wee bit faster. --Colin Mackay--
EuroCPian Spring 2004 Get Together[^] "You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar