Performance issues with Retrieving data

S

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
 
G

Guest

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 said:
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
Click to expand...
 
S

Sanjay Pais

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 said:
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 said:
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
Click to expand...
Click to expand...
 
W

William \(Bill\) Vaughn

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

Sanjay Pais

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

Raaj

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.
Click to expand...

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.

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




This did not make any difference. I actually found that it performed more
poorely when i specified CommandBehaviour.
Click to expand...
Would the fact that I have both GUIDS & datetime values as parameters/
column datatypes for the resultset have anything to do with this problem?
Click to expand...
Thanks for the quick reply all the same!
Sanjay
Click to expand...

Peter Bromberg said:
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
:
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

session state with user login2 1
Revisiting the datagrid data relation issue 7
Retrieving data from query using If, Then, Else 3
problem related to overloading == operator 3
Problem with SQLParameter data type (because 'System.Data.SqlClient.SqlParameter' is not derived fro 3
Threading guru required! 7
Problem with wrong data in dataset using SQLDataAdapter.fill 1

Top