Slow running query

Question

Slow running query

beath

SSCrazy

Points: 2939
More actions
September 18, 2003 at 3:53 am

#162368

I have a select query from multiple tables which when executed in QA locally on the server takes 6 seconds. However when I run the query in QA from the client tools on my desktop it takes 20 seconds.
It's returning 56,000 rows but this seems an excessive increase purely to blame on returning the data across the network.
The network card appears fine at 100MB but I'm not sure what counters to watch to see if there's a problem.
cheers

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

Consider the OPTION (FAST n) (See BOL) for your client. Is it absolutely necessary to return that many rows? Also, if you post the query, we could help try to optimize...

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Keep in ind the width of the data plays a big role in things as does what other traffic is going across the network. ALso depending on if you are using SQL 2000 it will take advantage of shared memory on the server (instead of network connection it does a kind of hook in local memory). Also, routers and other equipment will cause a slow down in the data movement especially with high loads otherwise. Plus what is the NIC on your local machine and what is it running at (if running at 10 you cannot use the 100MB from the server as you guage). ALso use Performance monitor to monitor throughput on the NIC, and dropped packets (these have to make another trip). Always expect an increase when not on the local box, many factors can play into how much.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

To add to the previous posts, maybe try a top 1, then top 1000, etc. and see if there is a linear increase at a time of low activity on the server/network.

philcart SSC-Forever Points: 47795 More actions · Answer 4

Another point to note in addition to the above, make sure you don't have "View Results in Grid" selected. QA tends to bog down pretty badly when viewing large resultsets in grid mode.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 5

To check the network you need the look at the object "network interface"

The counter "output queue length" must be zero. Packets should not need to be queued.

bytes total/ second should be considereable less than the bandwidth, maybe 60%.

Sometimes, if the network card automatically decides if it 10 or 100 Mbit, it takes 10 while 100 is possible. You can check the bandwidth with the counter "current bandwidth".

You could also check the packets ... discarded and errors counters

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 6

Beath, just interest sake, how many characters per row, plus minus, just to get an idea of the total amount of bytes being send to the client.

beath SSCrazy Points: 2939 More actions · Answer 7

Thanks for all the replies. Would you believe it "results in grid" was causing the problem. Sometime you look so hard the answer is staring you in the face.

cheers