Link to home
Start Free TrialLog in
Avatar of IUAATech
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(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.
ASKER CERTIFIED SOLUTION
Avatar of HappyFunBall
HappyFunBall

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of IUAATech
IUAATech

ASKER

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