Calendar display of information from database too slow

Discussion in 'ASP .Net Web Controls' started by Mark Schaver, Apr 21, 2004.

  1. Mark Schaver

    Mark Schaver Guest

    I'm using the calendar control to display information from a SQL
    Server database. I use a stored procedure to execute seven select
    statements, then use a datareader to display the information on the
    calendar. But it takes several minutes for the calendar to load, even
    though if I run the SQL for the stored procedure in query analyzer it
    takes only a few seconds to complete the query. I'm wondering if
    anyone has any suggestions on how I can make this calendar display
    faster. (I'm only an amateur coder, I'll admit, and I've been unable
    to find anything in the documentation or on the Web to help with
    this.) Thanks in advance.

    Here's my code:

    private void calFeatures_DayRender(object sender,
    System.Web.UI.WebControls.DayRenderEventArgs e)
    {
    SqlCommand sqlCmd = new SqlCommand("stp_featurecal", sqlConn);
    sqlCmd.CommandType = CommandType.StoredProcedure;

    System.Data.SqlClient.SqlDataReader DayReader;
    sqlConn.Open();
    DayReader = sqlCmd.ExecuteReader();
    string theDate;

    if (DayReader.Read())
    {
    while (DayReader.Read())
    {
    theDate = DayReader[1].ToString();
    if (theDate == e.Day.Date.ToString())
    {
    Label aLabel = new Label();
    aLabel.Text = "<br><strong><a href=update.asp?ID=" +
    DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
    + " - A1";
    e.Cell.Controls.Add(aLabel);
    }
    }
    }

    DayReader.NextResult();

    if (DayReader.Read())
    {
    while (DayReader.Read())
    {
    theDate = DayReader[1].ToString();
    if (theDate == e.Day.Date.ToString())
    {

    Label aLabel = new Label();
    aLabel.Text = "<br><strong><a href=update.asp?ID=" +
    DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
    + " - FF";
    e.Cell.Controls.Add(aLabel);
    }
    }
    }

    DayReader.NextResult();

    if (DayReader.Read())
    {
    while (DayReader.Read())
    {
    theDate = DayReader[1].ToString();
    if (theDate == e.Day.Date.ToString())
    {
    Label aLabel = new Label();

    aLabel.Text = "<br><strong><a href=update.asp?ID=" +
    DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
    + " - H&F";
    e.Cell.Controls.Add(aLabel);
    }
    }
    }

    DayReader.NextResult();

    if (DayReader.Read())
    {
    while (DayReader.Read())
    {
    theDate = DayReader[1].ToString();
    if (theDate == e.Day.Date.ToString())
    {
    Label aLabel = new Label();

    aLabel.Text = "<br><strong><a href=update.asp?ID=" +
    DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
    + " - WEx";
    e.Cell.Controls.Add(aLabel);
    }
    }
    }

    DayReader.NextResult();

    if (DayReader.Read())
    {
    while (DayReader.Read())
    {
    theDate = DayReader[1].ToString();
    if (theDate == e.Day.Date.ToString())
    {
    Label aLabel = new Label();

    aLabel.Text = "<br><strong><a href=update.asp?ID=" +
    DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
    + " - Scene";
    e.Cell.Controls.Add(aLabel);
    }
    }
    }

    DayReader.NextResult();

    if (DayReader.Read())
    {
    while (DayReader.Read())
    {
    theDate = DayReader[1].ToString();
    if (theDate == e.Day.Date.ToString())
    {
    Label aLabel = new Label();

    aLabel.Text = "<br><strong><a href=update.asp?ID=" +
    DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
    + " - H&G";
    e.Cell.Controls.Add(aLabel);
    }
    }
    }

    DayReader.NextResult();

    if (DayReader.Read())
    {
    while (DayReader.Read())
    {
    theDate = DayReader[1].ToString();
    if (theDate == e.Day.Date.ToString())
    {
    Label aLabel = new Label();

    aLabel.Text = "<br><strong><a href=update.asp?ID=" +
    DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
    + " - A&L";
    e.Cell.Controls.Add(aLabel);
    }
    }
    }

    DayReader.Close();
    sqlConn.Close();

    }
     
    Mark Schaver, Apr 21, 2004
    #1
    1. Advertising

  2. > I'm using the calendar control to display information from a SQL
    > Server database. I use a stored procedure to execute seven select
    > statements, then use a datareader to display the information on the
    > calendar. But it takes several minutes for the calendar to load, even
    > though if I run the SQL for the stored procedure in query analyzer it
    > takes only a few seconds to complete the query.


    Mark, I'd wager the problem is because the DayRender event fires for
    every day created by the calendar, so you are running the stored
    procedure like 35 times!

    What you want to do is run a procedure ONCE that gets ALL of the events
    for the selected month, ordered by the event date ascending. You can
    store this in a DataTable or DataSet.

    Then, in the DayRender event handler you can check to see if the current
    day matches up to the current event in the DataTable you are looking at.
    If it does, you display the event information and move ahead in the
    DataTable until you reach a record for a future date.

    This way, you have just one SQL access for the entire page. Hope this
    helps...

    --

    Scott Mitchell

    http://www.4GuysFromRolla.com
    http://www.ASPFAQs.com
    http://www.ASPMessageboard.com

    * When you think ASP, think 4GuysFromRolla.com!
     
    Scott Mitchell [MVP], Apr 21, 2004
    #2
    1. Advertising

  3. Mark Schaver

    Mark Schaver Guest

    As I said, I am an amateur ;-) (who has been trying to teach himself by
    reading your site and your books, by the way. You do great work).

    Thanks, that helps a lot. I didn't realize I was firing the stored
    procedure that many times. I had thought it was only firing once. Live
    and learn...



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Mark Schaver, Apr 21, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Martin
    Replies:
    0
    Views:
    475
    Martin
    Aug 4, 2004
  2. SB
    Replies:
    0
    Views:
    377
  3. Mike Foster
    Replies:
    0
    Views:
    909
    Mike Foster
    Jun 23, 2003
  4. Mark Parnell
    Replies:
    0
    Views:
    820
    Mark Parnell
    Jun 24, 2003
  5. Kevin Blount

    Database timeout too slow

    Kevin Blount, Jul 26, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    3,322
    Kevin Blount
    Jul 26, 2006
Loading...

Share This Page