running SqlCommand.ExecuteReader command is taking way too much time

Posted on 2006-04-28
Last Modified: 2012-08-13
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.
Question by:IUAATech
    LVL 9

    Accepted Solution

    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.

    Author Comment

    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.

    LVL 9

    Expert Comment

    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!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
    User art_snob ( encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now