Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Datagrid Control > Performance issues with Retrieving data

Reply
Thread Tools

Performance issues with Retrieving data

 
 
Sanjay Pais
Guest
Posts: n/a
 
      06-11-2007
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, ,

new System.Data.SqlClient.SqlParameter("@DateEnd",
System.Data.SqlDbType.DateTime, ,

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.SequentialAcc ess);

return DR;

}


Any ideas where the problem is?

Thanks in advance

Sanjay


 
Reply With Quote
 
 
 
 
Alvin Bruney [MVP]
Guest
Posts: n/a
 
      06-30-2007
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" <(E-Mail Removed)> wrote in message
news:%23H5L$(E-Mail Removed)...
>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, ,
>
> new System.Data.SqlClient.SqlParameter("@DateEnd",
> System.Data.SqlDbType.DateTime, ,
>
> 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.SequentialAcc ess);
>
> return DR;
>
> }
>
>
> Any ideas where the problem is?
>
> Thanks in advance
>
> Sanjay
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Performance Tutorials Services - Boosting Performance by DisablingUnnecessary Services on Windows XP Home Edition Software Engineer Javascript 0 06-10-2011 02:18 AM
Performance issues with Retrieving data Sanjay Pais ASP .Net 5 06-11-2007 08:29 PM
Data/File Structure and Algorithm for Retrieving Sorted Data Chunk Efficiently Jane Austine Python 14 10-09-2004 05:54 PM
- Re: Data/File Structure and Algorithm for Retrieving Sorted Data Chunk Efficiently Jane Austine Python 2 10-05-2004 01:54 PM
Web Form Performance Versus Single File Performance jm ASP .Net 1 12-12-2003 11:14 PM



Advertisments