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
5  1953 
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
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
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
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
>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 -
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: sandy |
last post by:
Hi All,
I am a newbie to MySQL and Python. At the first place, I would like to
know what are the general performance issues (if any) of using MySQL
with Python.
By performance, I wanted to...
|
by: Rodd Snook |
last post by:
I have an application which makes extensive use of the
Scripting.Dictionary object. I'm not doing anything silly like putting
them outside the page scope -- just creating quite a few of them and...
|
by: Robert |
last post by:
I am having performance issues on a SQL query in Access. My query is
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the...
|
by: Sebastian Werner |
last post by:
Howdy,
I currently develop the javascript toolkit qooxdoo
(http://qooxdoo.sourceforge.net), some of you heard it already.
We have discovered a slowdown on Internet Explorers performance when...
|
by: Rudolf Bargholz |
last post by:
Hi,
I have a ralatively simple SQL:
select FK from TABLE where upper(A) like 'B%' and upper(C) like 'D%'
We have DB2 UDB v7.1 FP 12 installed on Linux and on Windows 2003
On Linux using...
|
by: David W. Fenton |
last post by:
http://www.granite.ab.ca/access/performancefaq.htm
I hope Tony doesn't mind my opening a discussion of some issues on
his performance FAQ page here in the newsgroup. This is not meant as...
|
by: Mark Shelor |
last post by:
I've encountered a troublesome inconsistency in the C-language Perl
extension I've written for CPAN (Digest::SHA). The problem involves the
use of a static array within a performance-critical...
|
by: Tom Jastrzebski |
last post by:
Hello,
I was just testing VB.Net on Framework.Net 2.0 performance when I run into
the this problem.
This trivial code attached below executed hundreds, if not thousand times
faster in VB 6.0...
|
by: jwalsh604 |
last post by:
I have an Access db application that is intended to present the user
with the next available record to make an outbound phone call on.
Originally, I had set it up as a "split" database but, due to...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
