Pass-thru Query Fast to Display, but slow to move to lines lower in results.

Pass-thru Query Fast to Display, but slow to move to lines lower in results.

Post by BobM » Wed, 07 Dec 2005 04:08:06

I have a Pass-thru query that displays results almost immediately. But
if I try to look down in the table of results, it takes forever. Also
if I try to do a make table using the Pass-Thru query it takes hours to
make the table.

Is this normal? Any way to do a Make Table and take less time than a
standard Make Table query?

Pass-thru Query Fast to Display, but slow to move to lines lower in results.

Post by Tom Elliso » Wed, 07 Dec 2005 05:56:07

ear Bob:

I have a theory that may explain what you're experiencing.

Likely, the pass-through is to a client-server engine. The time taken to
display the first results reflects that the engine takes very little time to
produce results. To this time is ADDED the time it takes for the data to
pass over the network to your client. It is possible that the network speed
is insufficient for your performance requirements.

If this is the case, then you could either place the results in a table on
the server, or on your local machine. If you place them in a table on the
server, there will be a delay as you scroll down each time, and this will
not be significantly different from what you're experiencing now. If you
place the table on the local machine the delay will all occur at once as the
table is created. It will then scroll and be usable at local speeds. But
the total time will not significantly change.

If the users will be scrolling back and forth through a large amount of data
repeatedly, then the local table could be superior. Otherwise, it is more a
psychological difference. Would you rather the users wait a couple of
seconds every time they scroll, or a couple of minutes before they see
anything, but then be fast when they scroll?

The solution is probably more bandwidth.

Another possibility is that there is a latency problem in the network.
Latency is different from bandwidth. Bandwidth is like how fast a car goes.
Latency is like how long it takes to start the car.

Between a car with a top speed of 30 MPH that takes 2 seconds to start and a
car that goes 100 MPH but takes 2 minutes to start, which would win? Well,
it depends on how far the cars go between shutting off the engine. If they
stop and shut the car off every quarter of a mile, the 30 MPH car is going
to pass the 100 MPH car while the driver is going to try to start it.

If latency is the issue, putting a copy of the results on the local machine
will be a dramatic improvement.

Latency can be the issue over VPN. The internet connection between two
computers goes through many steps along the way to the remote computer. I
have seen latencies of several seconds. In a case where I was involved,
results took over an hour to arrive over VPN when it was done a little piece
at a time. When we changed to putting the server process all on the server
and then sending all the results at once (thereby reducing the total latency
by only "starting the car once for the whole race") the results were
available in 3-5 seconds.

Perhaps your question deserves some investigation from your network expert,
or some experimentation on your part. One experiment I'd recommend is to
put the client software on the server and observe it there. Put the client
software on a computer local to the server and observe that. This may tell
you the server is slow, or that the local network is slow or congested.
Such problems require some network expertise and understanding.

Tom Ellison

"BobM" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...