SQL query and adonet odd delay

[Mark Barton]

I'm developing a C# app that loads client account records. With normal
selection processing I still need to give the user around 50,000 records.
Running the stored procedure using query analyzer gives sub 1 second
response from the database and all is well. Loading the data into the app
using either a dataset or datareader (not used ie no bound controls or
reader loops) produces a strange delay of 10-12 seconds. By monitoring the
cpu and network traffic on both the client and server I have determined that
the server processes the query immediately, then both cpu's and the network
are idle for the 10 seconds or so and then the data is transferred in a few
hundred ms and the ado object is loaded immediately afterwards.

Thinking that it was a network issue I have been through the loop with hosts
file, different connection string options and sql server login types all to
no effect. Totally bemused I then removed the ORDER BY clause from the
stored procedure (index in place!) and the delay disappears and the results
are returned and loaded into the dataset almost as quickly as running Query
Analyzer. Infact much to my surprise adding the RowSort to the dataset had
no imapact at all. This does not make sense to me!

So why is there this delay when the stored procedure is called from adonet?

Mark

[colin]

The order by could be a "red herring" (Misleading assumption), unless ado
always wants the data in primary key order which it probably was before you
ordered it. If this was the case ado would need to undo the sort before
proceeding and re-order it before presenting to a dataset, it would account
for a delay while it was re-ordered but it would be daft for ms to implement
ado this way!

Do you get the same behaviour/response time when the database is local to
the app? there was a discussion here about tcpip permissions on the network
slowing things down when the server was remote and the suggestion here was
to present your data via a webservice to remove the lag caused by tcpip
permission checking

Further discussion here has suggested the fastest bulk load data transfer is
with File io, so if you really need 50k records consider output to XML from
your stored procedure and a read from xml into dataset on the client side.

I am always interested in other peoples ado perfromance threads as I dont
have access to large amounts of real data

Colin

"Mark Barton" <mar...@it-consultancy.co.uk> wrote in message
news:v2o0qje...@corp.supernews.com...

[William (Bill) Vaughn]

I don't think translating to and from a bulky Unicode string (XML) on both
ends will help performance. The fundamental problem is in the concept of
pulling over 50,000 rows. Yes, there are applications that need 50,000 data
points to draw a map, produce a report or construct a complex mathematical
model but if it's "normal" business data, the need for 50,000 rows is
usually not there. ADO (as we've discussed earlier) is not a good bulk copy
mechanism. If you need to import data, BCP or DTS is far better. XML is not.
In many cases, if you're producing reports, rolling up summary data on the
server can dramatically improve performance as well as performing running
totals as the data is initially captured.

hth

--
____________________________________
Bill Vaughn
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.
__________________________________

<colin> wrote in message news:uqZQ$7KwCHA.2472@TK2MSFTNGP12...

[Mark Barton]

I'm still investigating the problem and I have to agree that the Order By
may not be the root of the problem. I have a similar sympton when calling a
stored procedure with a JOIN in it. Again works fine with in SQL but when
called from adonet it hogs the SQLserver for several seconds. Changing the
sproc to remove the join and its fine. Now this is very odd, as I copied the
sproc code(with join) into a dataadapter in code and it executed fine,
changed the dataadapter to call the sproc and it took several seconds and
100% cpu on the server. The problem does not seem to be consistant and comes
and goes. Obviously with a small table its not noticeable.

On the large data set front I keep seeing posts about adonet performance and
reduce the size of data to solve the problem. In most cases I agree and do
that wherever possible, however the disconnected nature of adonet and the
concept of multi record edits and bulk saves goes against that concept and
in any case from a SQL Server and prospective there shouldn't be a problem.
Running Query analyzer I can transfer these 50,000 records from the server
to my pc in around 800ms, its adonet that seems to be the bottleneck. I
don't recall having these sorts of problems with C++ and ADO.

Mark

"William (Bill) Vaughn" <billvaRe...@nwlink.com> wrote in message
news:Oj$84MLwCHA.2516@TK2MSFTNGP09...

[William (Bill) Vaughn]

Run the query with the Show Plan option on in Query Analyzer and turn on the
profiler. Some of the problem might be that the data is cached after your
first test.
Yes, ADO.NET does create a complex data structure--the DataSet. One approach
you might consider is to leave the data on the server and create a
server-side cursor... Yes, it can be done, but you have to do so yourself
using CREATE CURSOR.

Consider as well that the server-side sort has to pass the entire rowset
through the ORDER BY mechanism fairly early in the query plan. If the rowset
is not that large, it's often faster to sort client-side.

--
____________________________________
Bill Vaughn
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.
__________________________________

"Mark Barton" <mar...@it-consultancy.co.uk> wrote in message
news:v2omul8...@corp.supernews.com...

[colin]

I agree with Bill,

try profiler and also try stepping through the proc with the debugger,
Ensure you are testing like for like ensure you use the same permissions to
connect Query analyser with the same username and password as the
application, use domain admin permissions if possible, try using mixed mode
security on the sql server with an sa account if possible, it will remove
any possible problems caused by windows authentication. Check the sql server
Processes and the lock type on your tables while debugging it sounds like
you could possibly have a concurrency issue if the behaviour is random. I
still think you should remove any remote server from the equation, try
installing msde Locally.

Also it might be worth trying to eliminate ado.net here try Access XP with a
pass through query running your proc, the idea is much the same run the
proc and from its output generate a indexed tableview.

Failing that Send me some data over email so I can play, 50,000 records in a
zipped delimited text

ColinR...@cix.REMOVE.co.uk

Colin

"William (Bill) Vaughn" <billvaRe...@nwlink.com> wrote in message

news:ufp0LPMwCHA.640@TK2MSFTNGP12...

[colin]

... and check for any stalled transactions on the server, can happen when
debugging

select @@trancount

Colin

<colin> wrote in message news:OC6q9sMwCHA.1620@TK2MSFTNGP11...

[colin]

...and, I tried playing with some of my data, it seems that local pc is
running out of resources trying to execute the query,grab the data, and
populate the dataset at the same time and is persisting the data into a
local cache the local drive is going like crazy and the network adapter is
retrieving data packets in bursts when resources allow.

so try reducing your query to get just primary key from table and see if
performance improved, Mine did Substantially

Colin

<colin> wrote in message news:e#nsjwMwCHA.2296@TK2MSFTNGP10...

[colin]

50000 records over the network to a slow sql2kserver(win2k, p4,500meg,with
256k,100 meg net card) primary key only took 3 seconds to get data and
populate a dataset

definatly no 10 second delay, it gets faster when query is cached, i used
straight sql not a stored proc

Colin

<colin> wrote in message news:upP6r8MwCHA.2604@TK2MSFTNGP12...