Performance issues with Retrieving data

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

Date:Mon, 11 Jun 2007 13:30:36 -0400 Author:

Re: Performance issues with Retrieving data

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$4ErHHA.4984@TK2MSFTNGP03.phx.gbl...

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

Date:Sat, 30 Jun 2007 18:35:47 -0400 Author: