Performance issues with Retrieving data

Discussion in 'ASP .Net' 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. try using the CommandBehavior.CloseConnection enum. SequentialAccess is for
    chunked data, which I don't believe is the case here.

    Peter
    --
    Site: http://www.eggheadcafe.com
    UnBlog: http://petesbloggerama.blogspot.com
    Short urls & more: http://ittyurl.net




    "Sanjay Pais" wrote:

    > 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
    >
    >
    >
    >
     
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=, Jun 11, 2007
    #2
    1. Advertising

  3. Sanjay Pais

    Sanjay Pais Guest

    This did not make any difference. I actually found that it performed more
    poorely when i specified CommandBehaviour.

    Would the fact that I have both GUIDS & datetime values as parameters/
    column datatypes for the resultset have anything to do with this problem?

    Thanks for the quick reply all the same!

    Sanjay

    "Peter Bromberg [C# MVP]" <> wrote in
    message news:...
    > try using the CommandBehavior.CloseConnection enum. SequentialAccess is
    > for
    > chunked data, which I don't believe is the case here.
    >
    > Peter
    > --
    > Site: http://www.eggheadcafe.com
    > UnBlog: http://petesbloggerama.blogspot.com
    > Short urls & more: http://ittyurl.net
    >
    >
    >
    >
    > "Sanjay Pais" wrote:
    >
    >> 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
    #3
  4. Fire up the Profiler to see what's getting executed by each interface.

    --
    ____________________________________
    William (Bill) Vaughn
    Author, Mentor, Consultant
    Microsoft MVP
    INETA Speaker
    www.betav.com/blog/billva
    www.betav.com
    Please reply only to the newsgroup so that others can benefit.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    __________________________________
    Visit www.hitchhikerguides.net to get more information on my latest book:
    Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
    and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
    -----------------------------------------------------------------------------------------------------------------------

    "Sanjay Pais" <> wrote in message
    news:%23%...
    > This did not make any difference. I actually found that it performed more
    > poorely when i specified CommandBehaviour.
    >
    > Would the fact that I have both GUIDS & datetime values as parameters/
    > column datatypes for the resultset have anything to do with this problem?
    >
    > Thanks for the quick reply all the same!
    >
    > Sanjay
    >
    > "Peter Bromberg [C# MVP]" <> wrote in
    > message news:...
    >> try using the CommandBehavior.CloseConnection enum. SequentialAccess is
    >> for
    >> chunked data, which I don't believe is the case here.
    >>
    >> Peter
    >> --
    >> Site: http://www.eggheadcafe.com
    >> UnBlog: http://petesbloggerama.blogspot.com
    >> Short urls & more: http://ittyurl.net
    >>
    >>
    >>
    >>
    >> "Sanjay Pais" wrote:
    >>
    >>> 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
    >>>
    >>>
    >>>
    >>>

    >
    >
     
    William \(Bill\) Vaughn, Jun 11, 2007
    #4
  5. Sanjay Pais

    Sanjay Pais Guest

    Found the culprit!!!

    I modified the stored procedure to use WITH (NOLOCK) and guess what?

    The performance changed from 24 seconds to less than a second or
    00:00:00.6420805 to be exact!

    And now for the flip side, is there anything I needs to now worry about
    because I am using this for reporting on the audit log.

    Thanks

    Sanjay

    "Sanjay Pais" <> wrote in message
    news:%23%...
    > This did not make any difference. I actually found that it performed more
    > poorely when i specified CommandBehaviour.
    >
    > Would the fact that I have both GUIDS & datetime values as parameters/
    > column datatypes for the resultset have anything to do with this problem?
    >
    > Thanks for the quick reply all the same!
    >
    > Sanjay
    >
    > "Peter Bromberg [C# MVP]" <> wrote in
    > message news:...
    >> try using the CommandBehavior.CloseConnection enum. SequentialAccess is
    >> for
    >> chunked data, which I don't believe is the case here.
    >>
    >> Peter
    >> --
    >> Site: http://www.eggheadcafe.com
    >> UnBlog: http://petesbloggerama.blogspot.com
    >> Short urls & more: http://ittyurl.net
    >>
    >>
    >>
    >>
    >> "Sanjay Pais" wrote:
    >>
    >>> 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
    #5
  6. Sanjay Pais

    Raaj Guest

    >And now for the flip side, is there anything I needs to now worry about
    >because I am using this for reporting on the audit log.


    By using WITH(NOLOCK) hint the sql is now retrieving the uncommitted
    read, so you may have to worry about the accuracy of the report.

    At times it may so appear (based on the updates to the underlying
    records) that report is rendering incorrect results.

    On Jun 11, 2:57 pm, "Sanjay Pais" <> wrote:
    > Found the culprit!!!
    >
    > I modified the stored procedure to use WITH (NOLOCK) and guess what?
    >
    > The performance changed from 24 seconds to less than a second or
    > 00:00:00.6420805 to be exact!
    >
    > And now for the flip side, is there anything I needs to now worry about
    > because I am using this for reporting on the audit log.
    >
    > Thanks
    >
    > Sanjay
    >
    > "Sanjay Pais" <> wrote in message
    >
    > news:%23%...
    >
    >
    >
    > > This did not make any difference. I actually found that it performed more
    > > poorely when i specified CommandBehaviour.

    >
    > > Would the fact that I have both GUIDS & datetime values as parameters/
    > > column datatypes for the resultset have anything to do with this problem?

    >
    > > Thanks for the quick reply all the same!

    >
    > > Sanjay

    >
    > > "Peter Bromberg [C# MVP]" <> wrote in
    > > messagenews:...
    > >> try using the CommandBehavior.CloseConnection enum. SequentialAccess is
    > >> for
    > >> chunked data, which I don't believe is the case here.

    >
    > >> Peter
    > >> --
    > >> Site: http://www.eggheadcafe.com
    > >> UnBlog: http://petesbloggerama.blogspot.com
    > >> Short urls & more: http://ittyurl.net

    >
    > >> "Sanjay Pais" wrote:

    >
    > >>> 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- Hide quoted text -

    >
    > - Show quoted text -
     
    Raaj, Jun 11, 2007
    #6
    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,693
    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,484
    =?Utf-8?B?Sm9obiBRIFNtaXRo?=
    Jan 16, 2004
  3. Jane Austine
    Replies:
    14
    Views:
    817
    Dennis Lee Bieber
    Oct 9, 2004
  4. Jane Austine
    Replies:
    2
    Views:
    474
    Changjune Kim
    Oct 5, 2004
  5. Sanjay Pais

    Performance issues with Retrieving data

    Sanjay Pais, Jun 11, 2007, in forum: ASP .Net Datagrid Control
    Replies:
    1
    Views:
    1,081
    Alvin Bruney [MVP]
    Jun 30, 2007
Loading...

Share This Page