Performance issues with Retrieving data

[Sanjay Pais]

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

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

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