SQL Server Timeout over the Network vs. Local

Hey Guys,

I'm hoping someone can help me out on this. We have a Stored Procedure
that when executed on the local server, through the app or query
analyzer, it runs instantaneous (returns over 9K rows).

If we run the same proc through the query analyzer it takes 40 seconds.
With our application it times out after 30 seconds. Yet if I osql in
across the network and do the same proc it starts getting results
instantly and completes in around 30 seconds.

I then simplified my test case to be 'select ID from IDTable' and got
the same results. I even put on the hint fastfirstrows, still network
using QA/App slow.

I understand the total time is related to sending the data back over
the wire, but what I don't get is why it didn't start sending the
results right away? Another oddity is that when I profile it the local
run has a .6 millisecond duration while the Network run has 40 seconds.

What am I missing? Why would I not get my first row back right away.
The query above is literally what I'm running. It has no sorting, and
the explain just shows a table scan on the PK. Is there some setting on
the server or ado connection that is forcing the response to come back
as a whole unit of work not individual pieces?

Thank in advance for any input.

.

Relevant Pages