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. Advertisements

  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


     
    Alvin Bruney [MVP], Jun 30, 2007
    #2
    1. Advertisements

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.