By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,684 Members | 1,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,684 IT Pros & Developers. It's quick & easy.

Performance issues with Retrieving data

P: n/a
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.SequentialAcc ess);

return DR;

}
Any ideas where the problem is?

Thanks in advance

Sanjay

Jun 11 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" wrote:
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.SequentialAcc ess);

return DR;

}
Any ideas where the problem is?

Thanks in advance

Sanjay

Jun 11 '07 #2

P: n/a
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 [C# MVP]" <pb*******@yahoo.yabbadabbadoo.comwrote in
message news:2B**********************************@microsof t.com...
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" wrote:
>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.SequentialAc cess);

return DR;

}
Any ideas where the problem is?

Thanks in advance

Sanjay


Jun 11 '07 #3

P: n/a
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" <sd****@athotmaildot.comwrote in message
news:%2******************@TK2MSFTNGP04.phx.gbl...
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 [C# MVP]" <pb*******@yahoo.yabbadabbadoo.comwrote in
message news:2B**********************************@microsof t.com...
>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" wrote:
>>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.SequentialA ccess);

return DR;

}
Any ideas where the problem is?

Thanks in advance

Sanjay



Jun 11 '07 #4

P: n/a
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" <sd****@athotmaildot.comwrote in message
news:%2******************@TK2MSFTNGP04.phx.gbl...
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 [C# MVP]" <pb*******@yahoo.yabbadabbadoo.comwrote in
message news:2B**********************************@microsof t.com...
>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" wrote:
>>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.SequentialA ccess);

return DR;

}
Any ideas where the problem is?

Thanks in advance

Sanjay



Jun 11 '07 #5

P: n/a
>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.
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.

On Jun 11, 2:57 pm, "Sanjay Pais" <sdp...@athotmaildot.comwrote:
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" <sdp...@athotmaildot.comwrote in message

news:%2******************@TK2MSFTNGP04.phx.gbl...
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 [C# MVP]" <pbromb...@yahoo.yabbadabbadoo.comwrote in
messagenews:2B**********************************@m icrosoft.com...
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" wrote:
>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.SequentialAc cess);
>return DR;
>}
>Any ideas where the problem is?
>Thanks in advance
>Sanjay- Hide quoted text -

- Show quoted text -

Jun 11 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.