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(connectionString);
        SqlCommand cmd = new SqlCommand("usp_MySP", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@uid", SqlDbType.NVarChar, 255));
        cmd.Parameters["@uid"].Value = uid;

        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

            //Get the first row.
            reader.Read();
            AlumniProfile ap = new AlumniProfile((string)reader["firstname"], (string)reader["lastname"],
                (string)reader["mid_name"], (string)reader["maiden_firstname"], (string)reader["maiden_lastname"],
                (string)reader["maiden_middlename"], (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.
IUAATechAsked:
Who is Participating?
 
HappyFunBallCommented:
How long does it take to run the procedure from SQL Query Analyzer?  I assume it will take about 3-4 seconds.  

If so, you may want to put an index on your ALUM_UNIV_ID_V table's UID column.
0
 
IUAATechAuthor Commented:
HappyFunBall,
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
0
 
HappyFunBallCommented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.