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;

            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

            //Get the first row.
            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"]);
            return ap;
        catch (SqlException err)
            throw new ApplicationException("SQL Error.");

This method is being called from code behind file of a form and my stored proc is as follows:

@uid      nvarchar(255)
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

With this, I get the following stats from enabling trace.

Category   Message       From First(s)                   From Last(s)
------------------------------------------------------------------------- Begin Load     0.000929729180357083   0.000118 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)
------------------------------------------------------------------------- Begin Load     0.000933530846596795      0.000120 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.
Who is Participating?
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.
IUAATechAuthor Commented:
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.

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!
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.