Subject Re: Slow connection to database / maybe need a middleware
Author Adam
--- In, Balanyi Zsolt
<balanyi_zsolt@...> wrote:
> Hello!
> We use ZeosLib to connect to FB 1.5 server over the Internet, and have
> the following problem:
> When we open a query, it really takes long time. After the query is
> opened, 10.000 records (consisting of five integers) are
transported in
> 1-2 seconds. As we have many tables, operations like refresh or
> scrolling with master/client datasets take long time because the
> are closed and opened again all over. Is there a way to speed up this
> procedure, or maybe I should use another database access component? I
> was also thinking in using middleware on the server, but I do not know
> if that would help...

Hello Zsolt,

Do some math first.

10000 records * 5 fields * 4 bytes per field ~ 200KB

Of course on top of that, you have the fact that the over the wire
protocol Firebird uses is quite inefficient. You have made no comment
about the query that returns 10000 records. Is it a simple select from
a single table that has a perfect index to use, or is it some horrible
query without a chance of running quickly.

There are some minor things you can try. Create a secure compressed
tunnel using Zebedee, which uses Stream compression. This may assist
the transfer speed to make it acceptable.

From your client application, you could create a delay on your master
scroll before the detail records are queried. For example, if the
cursor is on the record for 200ms, you could issue the request for the
detail records. This would allow very quick scrolling because the
request for detail records would never be sent until you stopped
moving, but as soon as they hit the record they want, the detail
records would be hit nearly immediately.

Aside from those two though, your problem is more conceptual. You have
an application clearly designed to run on a LAN, and as you are
discovering, the internet is slow (and often unreliable). If your
application requirement says that it must run in a slow network, then
your design must allow for this.

Another option is to use Terminal Services to deliver it to the remote
clients. This will allow you to keep your application in the
traditional client-server design and have quite acceptable performance
even on slow dialup connections.