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