IUAATech
asked on
running SqlCommand.ExecuteReader command is taking way too much time
I am trying to see why my site is running slow and after enabling trace, I have come to the conclusion that SqlComand.ExecuteReader() is the culprit.
My code is as follows:
public AlumniProfile GetAlumniProfile(string uid)
{
SqlConnection con = new SqlConnection(connectionSt ring);
SqlCommand cmd = new SqlCommand("usp_MySP", con);
cmd.CommandType = CommandType.StoredProcedur e;
cmd.Parameters.Add(new SqlParameter("@uid", SqlDbType.NVarChar, 255));
cmd.Parameters["@uid"].Val ue = uid;
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandB ehavior.Si ngleRow);
//Get the first row.
reader.Read();
AlumniProfile ap = new AlumniProfile((string)read er["firstn ame"], (string)reader["lastname"] ,
(string)reader["mid_name"] , (string)reader["maiden_fir stname"], (string)reader["maiden_las tname"],
(string)reader["maiden_mid dlename"], (string)reader["mail_label "], (string)reader["nickname"] );
reader.Close();
return ap;
}
catch (SqlException err)
{
throw new ApplicationException("SQL Error.");
}
finally
{
con.Close();
}
}
This method is being called from code behind file of a form and my stored proc is as follows:
CREATE PROCEDURE usp_MySP
@uid nvarchar(255)
AS
SELECT firstname, mid_name, lastname, ISNULL(maiden_firstname,'' ) maiden_firstname, maiden_lastname, maiden_middlename, mail_label, nickname FROM ALUM_UNIV_ID_V
WHERE uid = @uid
GO
With this, I get the following stats from enabling trace.
Category Message From First(s) From Last(s)
-------------------------- ---------- ---------- ---------- ---------- -------
aspx.page Begin Load 0.000929729180357083 0.000118
aspx.page End Load 5.11827762354789 5.117348
After commenting out the SqlDataReader reader = cmd.ExecuteReader() (along with other code related to reader), I get the following stats.
Category Message From First(s) From Last(s)
-------------------------- ---------- ---------- ---------- ---------- -------
aspx.page Begin Load 0.000933530846596795 0.000120
aspx.page End Load 1.22801889500239 1.227085
Keep in mind that I still have to create a AlumniProfile object. So I basically do something like AlumniProfile ap = new AlumniProfile("", "", "", "", "", "", "", "");
Why is there such a big difference? Why is the ExecuteReader taking such a long time? What are some other things I can investigate? Is there a problem with the database? Its on a different server. But both the machines are on a gigabit line.
Keep in mind that I am using ASP.NET 2.0 and I am running the code on ASP.NET development server. And also, I have debugging turned OFF.
Do I need to make some changes to my C# code? Any help on this would be greatly appreciated.
My code is as follows:
public AlumniProfile GetAlumniProfile(string uid)
{
SqlConnection con = new SqlConnection(connectionSt
SqlCommand cmd = new SqlCommand("usp_MySP", con);
cmd.CommandType = CommandType.StoredProcedur
cmd.Parameters.Add(new SqlParameter("@uid", SqlDbType.NVarChar, 255));
cmd.Parameters["@uid"].Val
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandB
//Get the first row.
reader.Read();
AlumniProfile ap = new AlumniProfile((string)read
(string)reader["mid_name"]
(string)reader["maiden_mid
reader.Close();
return ap;
}
catch (SqlException err)
{
throw new ApplicationException("SQL Error.");
}
finally
{
con.Close();
}
}
This method is being called from code behind file of a form and my stored proc is as follows:
CREATE PROCEDURE usp_MySP
@uid nvarchar(255)
AS
SELECT firstname, mid_name, lastname, ISNULL(maiden_firstname,''
WHERE uid = @uid
GO
With this, I get the following stats from enabling trace.
Category Message From First(s) From Last(s)
--------------------------
aspx.page Begin Load 0.000929729180357083 0.000118
aspx.page End Load 5.11827762354789 5.117348
After commenting out the SqlDataReader reader = cmd.ExecuteReader() (along with other code related to reader), I get the following stats.
Category Message From First(s) From Last(s)
--------------------------
aspx.page Begin Load 0.000933530846596795 0.000120
aspx.page End Load 1.22801889500239 1.227085
Keep in mind that I still have to create a AlumniProfile object. So I basically do something like AlumniProfile ap = new AlumniProfile("", "", "", "", "", "", "", "");
Why is there such a big difference? Why is the ExecuteReader taking such a long time? What are some other things I can investigate? Is there a problem with the database? Its on a different server. But both the machines are on a gigabit line.
Keep in mind that I am using ASP.NET 2.0 and I am running the code on ASP.NET development server. And also, I have debugging turned OFF.
Do I need to make some changes to my C# code? Any help on this would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can definitely see the time using SQL Profiler. You can also add "set statistics time on" in the query analyzer to get an elapsed time message to appear.
Glad I could help!
Glad I could help!
ASKER
you are totally correct. Why didn't I think of timing the procedure. I ran it from Query Analyzer and it took 4 seconds. Is it possible to know how many microseconds? I found this at the 'status bar' of the Query window. I guess I will need to put an index on the UID column.
Thanks for your insight.
Raj