Board index » delphi » Ways to increase IB server to client throughput

Ways to increase IB server to client throughput

Hello,

I was wondering whether I might be overlooking some means to increase the rate at which a query result set is
being transferred from the server to the client, both under Win NT4 and an Ethernet10 network.
I deliberately won't mention any other hardware or network specs since those are none of mine to change.
The configuration settings can be changed though. The current comm-protocol is IP.

Some query I use takes about 2 min to execute and another whopping 19' for its result set
( 3 000 000 records of about 40 bytes each) to be transferred.
(Using local Paradox tables, the transfer only takes 2'.)
I downloaded a document "Make IB scream" on the IB site and consequently increased the page size
and number of pages cached by the server but this doesn't have much influence.
I'm aware that a db is intended primarily to return small result sets quickly and that other db-servers
also take ages to do the same job.

Thanks for any suggestions

 

Re:Ways to increase IB server to client throughput


Quote
Youri Spitaels wrote:

> I was wondering whether I might be overlooking some means to
> increase the rate at which a query result set is
> being transferred from the server to the client, both under
> Win NT4 and an Ethernet10 network.
> I deliberately won't mention any other hardware or network specs
> since those are none of mine to change.

        Well, at the very least get them to spend $50 on a pair of 100 MBPS
Ethernet cards as this in and of itself can make your transfers 10 *
faster.

Quote
> Some query I use takes about 2 min to execute and another whopping 19' for its result set
> ( 3 000 000 records of about 40 bytes each) to be transferred.

        Don't do this.  You can't possibly need 3,000,000 records on the client
-- that's 120 MB of data.  The essence of good C/S design is sending as
little information as possible to the client.  You can tweak 'til the
cows come home and perhaps get your network traffic a little faster, but
this is nothing compared to the return you'll get by figuring out a way
to not do the transfer at all.

        If you need suggestions, please feel free to ask how to accomplish a
specific process.

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

Re:Ways to increase IB server to client throughput


Craig Stuntz (TeamB) <cstuntz@no_spam.vertexsoftware.com> wrote in message news:3B799920.6667EB6A@no_spam.vertexsoftware.com...

Quote

> > Some query I use takes about 2 min to execute and another whopping 19' for its result set
> > ( 3 000 000 records of about 40 bytes each) to be transferred.

> Don't do this.  You can't possibly need 3,000,000 records on the client
> -- that's 120 MB of data.  The essence of good C/S design is sending as
> little information as possible to the client.  You can tweak 'til the
> cows come home and perhaps get your network traffic a little faster, but
> this is nothing compared to the return you'll get by figuring out a way
> to not do the transfer at all.

> If you need suggestions, please feel free to ask how to accomplish a
> specific process.

Thanks Craig for the invitation. As a matter of fact a query probably isn't the right answer but it's the only one
I could come up with:
Those 120Megs are the result of a 3 table join and represent a network which has to be available in memory
to do shortest path computations. Network transfer or not, even looping over all the records of the result set
with Next(), ... 'till Eof with local IB is slow (using the IB express components, which are allready more
efficient than the standard VCL ones).
I vaguely recall reading somewhere that the IB API provides means for dumping large blocks of records
(used by Borland's Datapump utility) but I don't think it could be combined with an SQL query.
For the moment, the workaround I use is to execute the query just once and dump the result in a binary file.

Re:Ways to increase IB server to client throughput


Quote
In article <3b7cf3dc_2@dnews>, joeri.spita...@kpmg.be wrote...

Hi,

Quote
> Those 120Megs are the result of a 3 table join and represent a network which has to be available in memory
> to do shortest path computations. Network transfer or not, even looping over all the records of the result set
> with Next(), ... 'till Eof with local IB is slow

It will be, as you are still bringing the records to the client, even
though the sever is on the same machine.

Is there any reason why you can't do the calculations in a stored
procedure?  That way all the processing is done on the server (most
probably a better/powerful machine), and the records are not sent to the
client.

Dave

Other Threads