Performance issues with Retrieving data

Discussion in 'ASP .Net Datagrid Control' started by Sanjay Pais, Jun 11, 2007.

  1. Sanjay Pais

    Sanjay Pais Guest

    I have a table with over 1.3 million rows. I am retrieving only 20 at a time
    using the with - over clauses

    In query analyser, the data is retrieved in under a second.

    When retrieving using the data adaptor.fill or datareader to retrieve the
    data it takes over 24 seconds.
    public System.Data.SqlClient.SqlDataReader List1(int PageIndex, int
    PageSize, string ItemName, string UserIDs, DateTime DateStart, DateTime
    DateEnd, int status, string viewUserGroupIds)

    {

    SqlConnection objConn = new SqlConnection("data source=server;initial
    catalog=db;user id=user;password=pass;persist security info=True;");

    SqlCommand objCmd = new SqlCommand("dbo.spChangeLogRetrieveByUser",
    objConn);

    objCmd.CommandType = CommandType.StoredProcedure;

    objCmd.Parameters.AddRange(new System.Data.SqlClient.SqlParameter[] {

    new System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
    false, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current,
    null),

    new System.Data.SqlClient.SqlParameter("@PageIndex",
    System.Data.SqlDbType.Int, 4),

    new System.Data.SqlClient.SqlParameter("@PageSize",
    System.Data.SqlDbType.Int, 4),

    new System.Data.SqlClient.SqlParameter("@CountOnly",
    System.Data.SqlDbType.Bit, 1),

    new System.Data.SqlClient.SqlParameter("@UserIDs",
    System.Data.SqlDbType.VarChar, 1000),

    new System.Data.SqlClient.SqlParameter("@ItemName",
    System.Data.SqlDbType.VarChar, 200),

    new System.Data.SqlClient.SqlParameter("@DateStart",
    System.Data.SqlDbType.DateTime, 8),

    new System.Data.SqlClient.SqlParameter("@DateEnd",
    System.Data.SqlDbType.DateTime, 8),

    new System.Data.SqlClient.SqlParameter("@ItemID",
    System.Data.SqlDbType.UniqueIdentifier, 16),

    new System.Data.SqlClient.SqlParameter("@Status", System.Data.SqlDbType.Int,
    4),

    new System.Data.SqlClient.SqlParameter("@FileID",
    System.Data.SqlDbType.UniqueIdentifier, 16),

    new System.Data.SqlClient.SqlParameter("@ViewUserIDs",
    System.Data.SqlDbType.VarChar, 1000)});

    objCmd.Parameters["@PageIndex"].Value = PageIndex;

    objCmd.Parameters["@PageSize"].Value = PageSize;

    if (ItemName != "")

    {

    string itemName = ItemName;

    if ((itemName.Length > 1) && (itemName.IndexOf("*") != -1))

    {

    itemName = itemName.Replace("*", "%");

    objCmd.Parameters["@ItemName"].Value = itemName;

    }

    }

    else

    {

    objCmd.Parameters["@ItemName"].Value = System.DBNull.Value;

    }

    if (viewUserGroupIds != "")

    {

    objCmd.Parameters["@ViewUserIDs"].Value = viewUserGroupIds;

    }

    else

    {

    objCmd.Parameters["@ViewUserIDs"].Value = System.DBNull.Value;

    }

    if (UserIDs != "")

    {

    objCmd.Parameters["@UserIDs"].Value = UserIDs;

    }

    else

    {

    objCmd.Parameters["@UserIDs"].Value = System.DBNull.Value;

    }

    if (!DateStart.Equals(DateTime.MinValue))

    {

    objCmd.Parameters["@DateStart"].Value = Convert.ToDateTime(DateStart);

    }

    else

    {

    objCmd.Parameters["@DateStart"].Value = System.DBNull.Value;

    }

    if (!DateEnd.Equals(DateTime.MaxValue))

    {

    objCmd.Parameters["@DateEnd"].Value = Convert.ToDateTime(DateEnd);

    }

    else

    {

    objCmd.Parameters["@DateEnd"].Value = System.DBNull.Value;

    }

    if (status > 0)

    {

    objCmd.Parameters["@Status"].Value = status;

    }

    else

    {

    objCmd.Parameters["@Status"].Value = System.DBNull.Value;

    }

    objConn.Open();

    SqlDataReader DR = objCmd.ExecuteReader(CommandBehavior.SequentialAccess);

    return DR;

    }


    Any ideas where the problem is?

    Thanks in advance

    Sanjay
     
    Sanjay Pais, Jun 11, 2007
    #1
    1. Advertising

  2. How many objects are present in the dataset? It may be more than you think.

    --
    Regards,
    Alvin Bruney
    ------------------------------------------------------
    Shameless author plug
    Excel Services for .NET is coming...
    https://www.microsoft.com/MSPress/books/10933.aspx
    OWC Black Book www.lulu.com/owc
    Professional VSTO 2005 - Wrox/Wiley


    "Sanjay Pais" <> wrote in message
    news:%23H5L$...
    >I have a table with over 1.3 million rows. I am retrieving only 20 at a
    >time using the with - over clauses
    >
    > In query analyser, the data is retrieved in under a second.
    >
    > When retrieving using the data adaptor.fill or datareader to retrieve the
    > data it takes over 24 seconds.
    > public System.Data.SqlClient.SqlDataReader List1(int PageIndex, int
    > PageSize, string ItemName, string UserIDs, DateTime DateStart, DateTime
    > DateEnd, int status, string viewUserGroupIds)
    >
    > {
    >
    > SqlConnection objConn = new SqlConnection("data source=server;initial
    > catalog=db;user id=user;password=pass;persist security info=True;");
    >
    > SqlCommand objCmd = new SqlCommand("dbo.spChangeLogRetrieveByUser",
    > objConn);
    >
    > objCmd.CommandType = CommandType.StoredProcedure;
    >
    > objCmd.Parameters.AddRange(new System.Data.SqlClient.SqlParameter[] {
    >
    > new System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
    > System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
    > false, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current,
    > null),
    >
    > new System.Data.SqlClient.SqlParameter("@PageIndex",
    > System.Data.SqlDbType.Int, 4),
    >
    > new System.Data.SqlClient.SqlParameter("@PageSize",
    > System.Data.SqlDbType.Int, 4),
    >
    > new System.Data.SqlClient.SqlParameter("@CountOnly",
    > System.Data.SqlDbType.Bit, 1),
    >
    > new System.Data.SqlClient.SqlParameter("@UserIDs",
    > System.Data.SqlDbType.VarChar, 1000),
    >
    > new System.Data.SqlClient.SqlParameter("@ItemName",
    > System.Data.SqlDbType.VarChar, 200),
    >
    > new System.Data.SqlClient.SqlParameter("@DateStart",
    > System.Data.SqlDbType.DateTime, 8),
    >
    > new System.Data.SqlClient.SqlParameter("@DateEnd",
    > System.Data.SqlDbType.DateTime, 8),
    >
    > new System.Data.SqlClient.SqlParameter("@ItemID",
    > System.Data.SqlDbType.UniqueIdentifier, 16),
    >
    > new System.Data.SqlClient.SqlParameter("@Status",
    > System.Data.SqlDbType.Int, 4),
    >
    > new System.Data.SqlClient.SqlParameter("@FileID",
    > System.Data.SqlDbType.UniqueIdentifier, 16),
    >
    > new System.Data.SqlClient.SqlParameter("@ViewUserIDs",
    > System.Data.SqlDbType.VarChar, 1000)});
    >
    > objCmd.Parameters["@PageIndex"].Value = PageIndex;
    >
    > objCmd.Parameters["@PageSize"].Value = PageSize;
    >
    > if (ItemName != "")
    >
    > {
    >
    > string itemName = ItemName;
    >
    > if ((itemName.Length > 1) && (itemName.IndexOf("*") != -1))
    >
    > {
    >
    > itemName = itemName.Replace("*", "%");
    >
    > objCmd.Parameters["@ItemName"].Value = itemName;
    >
    > }
    >
    > }
    >
    > else
    >
    > {
    >
    > objCmd.Parameters["@ItemName"].Value = System.DBNull.Value;
    >
    > }
    >
    > if (viewUserGroupIds != "")
    >
    > {
    >
    > objCmd.Parameters["@ViewUserIDs"].Value = viewUserGroupIds;
    >
    > }
    >
    > else
    >
    > {
    >
    > objCmd.Parameters["@ViewUserIDs"].Value = System.DBNull.Value;
    >
    > }
    >
    > if (UserIDs != "")
    >
    > {
    >
    > objCmd.Parameters["@UserIDs"].Value = UserIDs;
    >
    > }
    >
    > else
    >
    > {
    >
    > objCmd.Parameters["@UserIDs"].Value = System.DBNull.Value;
    >
    > }
    >
    > if (!DateStart.Equals(DateTime.MinValue))
    >
    > {
    >
    > objCmd.Parameters["@DateStart"].Value = Convert.ToDateTime(DateStart);
    >
    > }
    >
    > else
    >
    > {
    >
    > objCmd.Parameters["@DateStart"].Value = System.DBNull.Value;
    >
    > }
    >
    > if (!DateEnd.Equals(DateTime.MaxValue))
    >
    > {
    >
    > objCmd.Parameters["@DateEnd"].Value = Convert.ToDateTime(DateEnd);
    >
    > }
    >
    > else
    >
    > {
    >
    > objCmd.Parameters["@DateEnd"].Value = System.DBNull.Value;
    >
    > }
    >
    > if (status > 0)
    >
    > {
    >
    > objCmd.Parameters["@Status"].Value = status;
    >
    > }
    >
    > else
    >
    > {
    >
    > objCmd.Parameters["@Status"].Value = System.DBNull.Value;
    >
    > }
    >
    > objConn.Open();
    >
    > SqlDataReader DR = objCmd.ExecuteReader(CommandBehavior.SequentialAccess);
    >
    > return DR;
    >
    > }
    >
    >
    > Any ideas where the problem is?
    >
    > Thanks in advance
    >
    > Sanjay
    >
    >
     
    Alvin Bruney [MVP], Jun 30, 2007
    #2
    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. JMaelstrom

    IIS 6 vs IIS 5 ASP.NET Performance Issues

    JMaelstrom, Dec 9, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    4,678
    shan420
    Apr 30, 2010
  2. John Q. Smith

    SQLServer session state performance issues

    John Q. Smith, Jan 12, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    3,469
    =?Utf-8?B?Sm9obiBRIFNtaXRo?=
    Jan 16, 2004
  3. Jane Austine
    Replies:
    14
    Views:
    806
    Dennis Lee Bieber
    Oct 9, 2004
  4. Jane Austine
    Replies:
    2
    Views:
    467
    Changjune Kim
    Oct 5, 2004
  5. Sanjay Pais

    Performance issues with Retrieving data

    Sanjay Pais, Jun 11, 2007, in forum: ASP .Net
    Replies:
    5
    Views:
    409
Loading...

Share This Page