Long Running Query

I have a SP, which displays results within 15 secs on local SQL server, but when trying execute from other SQL Client, it takes more than 5 mins. And I don't find any network issues. Can anybody help me to isolate the issu
Thank
Bhagyahow much data including messages is being returned by the
sp,
if very little, then it probably is not a network issue.
if a lot, then everything points to a network issue.
transfer a large file (~100MB) between the client and
server, how long does it take? Fast Ethernet at
100Mbit/sec in full-duplex should move data between 5-
10MByte/sec depending on the number of hops.
if you are not on Fast Ethernet with Full-duplex mode,
then it may very well take forever.
given that FE switches are so inexpensive, i would not
bother trying other tricks to make this work on half-duplex
>--Original Message--
>I have a SP, which displays results within 15 secs on
local SQL server, but when trying execute from other SQL
Client, it takes more than 5 mins. And I don't find any
network issues. Can anybody help me to isolate the issue
>Thanks
>Bhagya
>.
>|||Hi
Thanks, for the reply
But the data that is returned by SP is hardly 50kb. What could be the reason for the delay
Thank
Bhagya|||Do you have SET NOCOUNT ON?
--
Andrew J. Kelly
SQL Server MVP
"Bhagya" <anonymous@.discussions.microsoft.com> wrote in message
news:DAC51A3C-550C-4163-AADB-86BDC8270F47@.microsoft.com...
> Hi,
> Thanks, for the reply.
> But the data that is returned by SP is hardly 50kb. What could be the
reason for the delay.
> Thanks
> Bhagya|||15 sec on the local server is still an expensive query.
what is it doing?
does the execution plan show a hash match or hash join?
if so, are more than 10K rows in hash ? regardless of the
final row count.
when you say local, are you running the sp from QA,
from other clients: is that QA or your own application
>--Original Message--
>Hi,
>Thanks, for the reply.
>But the data that is returned by SP is hardly 50kb. What
could be the reason for the delay.
>Thanks
>Bhagya
>.
>|||Hi Joe
Basically this SP is returning the data used in a report. It is doing Hash match / Part Aggregate join. There are about 5000000 row in the Hash
The table has about more than 10000000 rows and it is indexed, as per requirement, and columns used in joins and filter
Thank
Bhagya|||If it is doing a Hash match then you probably don't have proper indexes but
that is not always the case. How may rows are you actually returning to the
client? Sounds like a lot. The more rows you return the longer it will
take, especially if the client can't handle them fast enough.
Andrew J. Kelly
SQL Server MVP
"Bhagya" <anonymous@.discussions.microsoft.com> wrote in message
news:FD457C8C-D6E2-4AFF-A92A-3F8D19D7CC89@.microsoft.com...
> Hi Joe,
> Basically this SP is returning the data used in a report. It is doing
Hash match / Part Aggregate join. There are about 5000000 row in the Hash.
> The table has about more than 10000000 rows and it is indexed, as per
requirement, and columns used in joins and filters
> Thanks
> Bhagya|||Bhagya,
Is it possible that you are seeing this bug (does your query have a
LIKE operator, and are you using SQL Server 7.0?)
http://support.microsoft.com/default.aspx?scid=kb;en-us;814115&Product=sql2k
SK
Bhagya wrote:
>Hi Joe,
>Basically this SP is returning the data used in a report. It is doing Hash match / Part Aggregate join. There are about 5000000 row in the Hash.
>The table has about more than 10000000 rows and it is indexed, as per requirement, and columns used in joins and filters
>Thanks
>Bhagya
>sql