.Net slow, SQL tools FAST??

[Guest]

I am running an sproc that runs in 2-3 sec using query analyzer, but takes just under 5 min using .net data providers (I've used SqlClient, OleDb, and Data block v2

t-sql statement executed from QA

RptInvRoyalty 126, '2/1/2004', '2/29/2004

..net code

System.Data.SqlClient.SqlCommand cmd
cmd = new System.Data.SqlClient.SqlCommand("RptInvRoyalty 126, '2/1/2004', '2/29/2004'")
cmd.Connection = new System.Data.SqlClient.SqlConnection(cnnSql)
cmd.CommandTimeout = 300
cmd.Connection.Open()
tr

grid.DataSource = cmd.ExecuteReader(); // this line takes just under 5 min to execut
grid.DataBind()

finall

cmd.Connection.Close()


Please help..

Thanks
Josh

 

[Guest]

Josh,

Simply try filling a DataTable or DataSet object rather than binding to the grid. I haven't used DataReaders much, but I think this might be the source of your problems. I can find no reason that ADO.Net itself should act in such a manner. Try something along these lines and time this to narrow down the problem to ADO.net or the actual databinding (beware, Outlook intellisense)

using System.Data.SqlClient

SqlDataAdapter adapter = new SqlDataAdapder()
System.Data.DataSet ds = new System.Data.DataSet()
SqlConnection cn = new SqlConnection(cnnSql)
SqlCommand cmd = new SqlCommand("RptInvRoyalty 126, '2/1/2004', '2/29/2004'")
cmd.Connection = cn
adapter.SelectCommand = cmd
try
cn.Open()
adapter.Fill( ds ); // time thi
} finally
cn.Close()


grid.DataSource = ds.Tables[0]; // time this, is this the bottleneck
grid.DataBind()

Another suggestion, I've never actually called a stored procedure in the manner above. If this doesn't yield satisfactory results, try to explicitly set cmd.CommandType = CommandType.StoredProc, the default is CommandType.Text. It might take a while for SqlServer to determine that is actually calling a stored procedure. Addtionally, look into the SqlCommand.Parameters and create and set parameters to pass values to the stored procedure. I have used this approach with great success and speed

HTH,

Jef

 

[Miha Markic [MVP C#]]

Hi,

Yeah, just like Jeff said, comment grid.DataBind() line and see what
happens.
You might also consider timing only the ExecutReader line without
Connction.Open() as it takes some time.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I am running an sproc that runs in 2-3 sec using query analyzer, but takes

just under 5 min using .net data providers (I've used SqlClient, OleDb, and
Data block v2)

 

[Guest]

I have tried what he suggested, I have made the call through SqlHelper.ExecuteDataSet with the same result

I have stepped through the code and it is the execute commands, not the binding that takes a long time

I have tried calling it with commandtype = sproc and passing the the params

If I set the CommandTimeout = 120 then the execute operations time out and it never gets to the databinding line. The operation should not even take 2 min when it only takes 1-3 sec in QA

Any other pointers

Thanks
Jos


----- Miha Markic [MVP C#] wrote: ----

Hi

Yeah, just like Jeff said, comment grid.DataBind() line and see wha
happens
You might also consider timing only the ExecutReader line withou
Connction.Open() as it takes some time

--
Miha Markic [MVP C#] - RightHand .NET consulting & software developmen
miha at rthand co
www.rthand.co

I am running an sproc that runs in 2-3 sec using query analyzer, but take

just under 5 min using .net data providers (I've used SqlClient, OleDb, an
Data block v2

 

[Guest]

I had many problems with dataadapter. So I replaced it with DataReader. There is lot of coding involved when you have to show data in a grid because easiest way to do it is bind it to DataSet. So this is what I do. I am sure your code will work this way too.

//This is my own class to capture NameObject collectio
NameObjectCollection retList = new NameObjectCollection()
SqlDataReader redr
conn = new SqlConnection(connString)
conn.Open()
cmd.Connection = conn
redr = cmd.ExecuteReader(); // cmd is passed as a paramete
If (redr == null

throw new Exception("Unable to populate reader obj")

cmd.Dispose()

while (redr.Read()

NameObjectCollection record = new NameObjectCollection()
while (true

tr

ObjValue=redr[columnCount]
columnCount++
record.Add("Column" + columnCount.ToString(), ObjValue)

catch (Exception

break


columnCount = 0
rowCount++
retList.Add(rowCount.ToString(), record)

redr.Close()
conn.Dispose()
return retList

--now create datatable objec
--create column objects and add 'em to datatable
--loop thru the collection returned by the code above (It is actually collection of Collections) an
--populate data in the datatabl
--close the collection object
--bind it to your datagri
--close datatable

When I did same thing with dataset populated using Dataadapter, when the more 20-30 users tried to run it, it would throw and object reference error and datasets were not populated.

HTH

Sach
----- JoshuaLuke wrote: ----

I have tried what he suggested, I have made the call through SqlHelper.ExecuteDataSet with the same result

I have stepped through the code and it is the execute commands, not the binding that takes a long time

I have tried calling it with commandtype = sproc and passing the the params

If I set the CommandTimeout = 120 then the execute operations time out and it never gets to the databinding line. The operation should not even take 2 min when it only takes 1-3 sec in QA

Any other pointers

Thanks
Jos


----- Miha Markic [MVP C#] wrote: ----

Hi

Yeah, just like Jeff said, comment grid.DataBind() line and see wha
happens
You might also consider timing only the ExecutReader line withou
Connction.Open() as it takes some time

--
Miha Markic [MVP C#] - RightHand .NET consulting & software developmen
miha at rthand co
www.rthand.co

I am running an sproc that runs in 2-3 sec using query analyzer, but take

just under 5 min using .net data providers (I've used SqlClient, OleDb, an
Data block v2

 

[Miha Markic [MVP C#]]

Hi Joshua,

You might send me a simple sample with database so I can test it here.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I have tried what he suggested, I have made the call through

SqlHelper.ExecuteDataSet with the same result.

I have stepped through the code and it is the execute commands, not the

binding that takes a long time.

I have tried calling it with commandtype = sproc and passing the the params.

If I set the CommandTimeout = 120 then the execute operations time out and

it never gets to the databinding line. The operation should not even take 2
min when it only takes 1-3 sec in QA.

Any other pointers?

Thanks,
Josh


----- Miha Markic [MVP C#] wrote: -----

Hi,

Yeah, just like Jeff said, comment grid.DataBind() line and see what
happens.
You might also consider timing only the ExecutReader line without
Connction.Open() as it takes some time.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I am running an sproc that runs in 2-3 sec using query analyzer,

but takes
just under 5 min using .net data providers (I've used SqlClient, OleDb, and
Data block v2)

cmd = new System.Data.SqlClient.SqlCommand("RptInvRoyalty

126,
'2/1/2004', '2/29/2004'");

cmd.Connection = new System.Data.SqlClient.SqlConnection(cnnSql);
cmd.CommandTimeout = 300;
cmd.Connection.Open();
try
{
grid.DataSource = cmd.ExecuteReader(); // this line takes

just
under 5 min to execute

grid.DataBind();
}
finally
{
cmd.Connection.Close();
}
Josh

 

[Michael Hampel]

Try creating parameters for you command rather than passing it as one
string. It could be that some stange coversion is happening.

 

[Guest]

Done that, no change in processing time

Thanks th


----- Michael Hampel wrote: ----

Try creating parameters for you command rather than passing it as on
string. It could be that some stange coversion is happening

 

[Guest]

I've tried it this way, the line cmd.ExecuteReader(); takes about 300 sec to execute

Thanks
Jos

----- Sachy wrote: ----

I had many problems with dataadapter. So I replaced it with DataReader. There is lot of coding involved when you have to show data in a grid because easiest way to do it is bind it to DataSet. So this is what I do. I am sure your code will work this way too.

//This is my own class to capture NameObject collectio
NameObjectCollection retList = new NameObjectCollection()
SqlDataReader redr
conn = new SqlConnection(connString)
conn.Open()
cmd.Connection = conn
redr = cmd.ExecuteReader(); // cmd is passed as a paramete
If (redr == null

throw new Exception("Unable to populate reader obj")

cmd.Dispose()

while (redr.Read()

NameObjectCollection record = new NameObjectCollection()
while (true

tr

ObjValue=redr[columnCount]
columnCount++
record.Add("Column" + columnCount.ToString(), ObjValue)

catch (Exception

break


columnCount = 0
rowCount++
retList.Add(rowCount.ToString(), record)

redr.Close()
conn.Dispose()
return retList

--now create datatable objec
--create column objects and add 'em to datatable
--loop thru the collection returned by the code above (It is actually collection of Collections) an
--populate data in the datatabl
--close the collection object
--bind it to your datagri
--close datatable

When I did same thing with dataset populated using Dataadapter, when the more 20-30 users tried to run it, it would throw and object reference error and datasets were not populated.

HTH

Sach
----- JoshuaLuke wrote: ----

I have tried what he suggested, I have made the call through SqlHelper.ExecuteDataSet with the same result

I have stepped through the code and it is the execute commands, not the binding that takes a long time

I have tried calling it with commandtype = sproc and passing the the params

If I set the CommandTimeout = 120 then the execute operations time out and it never gets to the databinding line. The operation should not even take 2 min when it only takes 1-3 sec in QA

Any other pointers

Thanks
Jos


----- Miha Markic [MVP C#] wrote: ----

Hi

Yeah, just like Jeff said, comment grid.DataBind() line and see wha
happens
You might also consider timing only the ExecutReader line withou
Connction.Open() as it takes some time

--
Miha Markic [MVP C#] - RightHand .NET consulting & software developmen
miha at rthand co
www.rthand.co

I am running an sproc that runs in 2-3 sec using query analyzer, but take

just under 5 min using .net data providers (I've used SqlClient, OleDb, an
Data block v2

 

[Cowboy \(Gregory A. Beamer\)]

Run a SQL profiler and find out what is being sent to the database. This
will give you a better idea of what is going on. You might also want to
profile the type of connection, as you may need to adjust which library is
being used to connect to the database. Until you have the delta between QA
and .NET, you do not really know what you are dealing with.

I would also suggest completely parameterizing (as others have suggested),
as you reduce parse time and look at whether a DataSet changes anything
(underlying, the Reader is still there, but having it run completely the MS
way may alter things).

Not sure this will acomplish anything, but I have traditionally done the
DataReader more like:

DataReader dr = cmd.ExecuteReader();
Grid1.DataSource = dr;

This shouldn't affect anything. It is more for maintainability, as a junior
dev can easily understand what is being accomplished. If it helps, it is
wonderful.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

I am running an sproc that runs in 2-3 sec using query analyzer, but takes

just under 5 min using .net data providers (I've used SqlClient, OleDb, and
Data block v2)

 

[Guest]

I've run into a situation where an ExecuteReader is taking around 20 - 30 seconds to run a stored SQL procedure whereas if I execute the stored procedure from inside Enterprise Manager's Query Analyser it runs in less than 1 second

Below is the VB .NET property I use inside of a class object. The idea is that the Revenue property returns a single value which is pulled from an SQL database. The strConnectionStr is a Shared variable within the class and has a valid value when the Revenue property is called

ReadOnly Property Revenue(ByVal startdate As Date, ByVal enddate As Date, ByVal intCss As Integer, ByVal blnBilled As Boolean) As Singl
Ge
Dim conSQL As New SqlClient.SqlConnection(strConnectionStr
Dim drDetails As SqlClient.SqlDataReade

conSQL.Open(

With conSQL.CreateComman
If blnBilled The
.CommandText = "parmsel_team_revenue @team=" & intTeamNum & ", @sdate='"
& startdate.ToShortDateString & "',@edate='" & enddate.ToShortDateString & "',@css="
& intCss & ", @billed=1
Els
.CommandText = "parmsel_team_revenue @team=" & intTeamNum & ", @sdate='"
& startdate.ToShortDateString & "',@edate='" & enddate.ToShortDateString & "',@css="
& intCss & ", @billed=0
End I

drDetails = .ExecuteReader(CommandBehavior.SingleRow
.Dispose(
End Wit

If drDetails.HasRows The
drDetails.Read(
Revenue = drDetails("Revenue"
Els
Revenue =
End I

drDetails.Close(
conSQL.Close(
conSQL = Nothin
End Ge
End Property

 

[Greg Low \(MVP\)]

Hi Tony,

My guess is that it's the connection open() that's taking the time here (for
some reason). It's the only thing that could take that sort of time unless
the proc is complex.

In addition, can I suggest the following:

1. It's important to set the CommandType for the command when calling a
stored proc eg. .CommandType = CommandType.StoredProcedure.
2. ToShortDateString isn't a great method for formatting date parameters.
Recommend having a function that formats them as 'YYYYMMDD'.

HTH,

--
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com

I've run into a situation where an ExecuteReader is taking around 20 - 30

seconds to run a stored SQL procedure whereas if I execute the stored
procedure from inside Enterprise Manager's Query Analyser it runs in less
than 1 second.

Below is the VB .NET property I use inside of a class object. The idea is

that the Revenue property returns a single value which is pulled from an SQL
database. The strConnectionStr is a Shared variable within the class and
has a valid value when the Revenue property is called.

ReadOnly Property Revenue(ByVal startdate As Date, ByVal enddate As Date,

ByVal intCss As Integer, ByVal blnBilled As Boolean) As Single

Get
Dim conSQL As New SqlClient.SqlConnection(strConnectionStr)
Dim drDetails As SqlClient.SqlDataReader

conSQL.Open()

With conSQL.CreateCommand
If blnBilled Then
.CommandText = "parmsel_team_revenue @team=" & intTeamNum & ", @sdate='"
& startdate.ToShortDateString &

"',@edate='" & enddate.ToShortDateString & "',@css="

& intCss & ", @billed=1"
Else
.CommandText = "parmsel_team_revenue @team=" & intTeamNum & ", @sdate='"
& startdate.ToShortDateString &

"',@edate='" & enddate.ToShortDateString & "',@css="

 

[Guest]

Thank you for the response Greg, I am currently looking at applying your suggestions
To follow up though I had a question as to the complexity of my SQL procedure. I don't see it as being too complex but I do have subqueries in the SELECT. I don't see this as being complex but would you condsider this as complex enough to cause the 20 - 30 second discrepency. If so, then I'm still curious why a call from .NET cannot process as fast as a call made directly in Query Analyzer

Thanks again, here is the SQL inside of my stored procedure

SELECT ISNULL(SUM(ARTAMT),0) As 'Revenue
FROM TransactionsBille
WHERE ARTDATE >= @sdate and
ARTDATE <= @edate and
ARTCUST IN
(SELECT ARCUST FROM ARMAST WHERE
ARSM IN
(SELECT User_Code_VC FROM Users WHERE Team_IN = @team

 

[Patrice]

What if you use SQL Server profiler to see the exact SQL sent and try then
to submit the same SQL ?

Patrice

Thank you for the response Greg, I am currently looking at applying your suggestions.
To follow up though I had a question as to the complexity of my SQL

procedure. I don't see it as being too complex but I do have subqueries in
the SELECT. I don't see this as being complex but would you condsider this
as complex enough to cause the 20 - 30 second discrepency. If so, then I'm
still curious why a call from .NET cannot process as fast as a call made
directly in Query Analyzer.