Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

running SqlCommand.ExecuteReader command is taking way too much time

Posted on 2006-04-28
3
Medium Priority
?
3,091 Views
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;

        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.
0
Comment
Question by:IUAATech
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
HappyFunBall earned 1000 total points
ID: 16565402
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
 

Author Comment

by:IUAATech
ID: 16565469
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
 
LVL 9

Expert Comment

by:HappyFunBall
ID: 16565534
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

580 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