SqlDataAdapter Fill Dataset is slow

[chrisdepalma]

I call a stored procedure that returns two select statements.

in query analylzer, it takes 1 second to do the query.

The following code takes 60 seconds in dot net 1.1. It seems to me
that the dot net runtime sucks. Any ideas?


SqlConnection con = GetConnection();
con.Open();
SqlCommand cm = new SqlCommand(PstrSQL,con);
cm.CommandTimeout = 0;
cm.CommandType= CommandType.Text;

SqlDataAdapter da = new SqlDataAdapter(cm);

DataSet ds = new DataSet();

da.Fill(ds);
da.Dispose();
cm.Dispose();
con.Close();
con.Dispose();
return ds;

 

[WJ]

I call a stored procedure that returns two select statements.

in query analylzer, it takes 1 second to do the query.

Keep in mind that you need to count the time it takes to connect to SQL
instance too from query analyzer (1st time you fired it up). How long did it
take ? Once connection is established, sqlanl. is very fast because it is
statefull. So you cannot compare this tool with your application.

The following code takes 60 seconds in dot net 1.1. It seems to me
that the dot net runtime sucks. Any ideas?

yep. clr is "suck" full time. It also depends on your network too. Are you
running locally like win Xp, sql and IIS on the same box as developer pc ?
is it a webform or windform ? Winform is very fast, faster than .aspx form.

SqlConnection con = GetConnection();

What involved in GetConnection() method ?

SqlCommand cm = new SqlCommand(PstrSQL,con);

is PstrSQL very comlex or just a plain select * from sometable ? Your table
big ? Is it indexed ?


John

 

[CD]

Here is the code for getconnection:
static string MstrConnString =
"server=(local);Trusted_Connection=yes;database=Stocks2";

public static SqlConnection GetConnection()
{
return new SqlConnection(MstrConnString);
}

Nothing profound. I am executing locally. SQL on same machine as .net
client. Opening the connection does not take the time. It is blocking
on teh fill() call.

The PstrSQL is the string is a simple call to the proc with parameters
"Execute Proc 1,27,29"

I tried disconnecting in query analyzer and rerunning the query and it
took 21 seconds. Still much faster than dataset fill()

Go figure.

 

[ijustok]

PstrSQL ="Execute dbo.Proc 1,27,29"

Here is the code for getconnection:
static string MstrConnString =
"server=(local);Trusted_Connection=yes;database=Stocks2";

public static SqlConnection GetConnection()
{
return new SqlConnection(MstrConnString);
}

Nothing profound. I am executing locally. SQL on same machine as .net
client. Opening the connection does not take the time. It is blocking
on teh fill() call.

The PstrSQL is the string is a simple call to the proc with parameters
"Execute Proc 1,27,29"

I tried disconnecting in query analyzer and rerunning the query and it
took 21 seconds. Still much faster than dataset fill()

Go figure.

 

[chrisdepalma]

I put these statements in the top of my stored proc and it seemed to
work now.

set nocount off
set arithabort on
set concat_null_yields_null on
set ansi_nulls on
set cursor_close_on_commit off
set ansi_null_dflt_on on
set implicit_transactions off
set ansi_padding on
set ansi_warnings on
set quoted_identifier on

I copied these from the profiler when QA called the proc. I have no
idea which one did the trick, but i don;t care since the db i use is
read only. I suspect it was set implicit_transactions off

 

[W.G. Ryan eMVP]

Even after turning everything off, it's not an apples to apples comparison.
Fire your query in query analyzer - then pull the network plug on your
computer out. Scroll down to the next page's worth of records. Won't work
right? but it will with a DataAdapter and fill b/c everythign's been
completely cached. Two different comparisons on this one.

 

[CD]

I don't know what you are talking about. Network cable is irrelevant.
I am doing everything on one machine. It worked anyway.