Subject Re: Really slow database performance
Author phil_hhn
Hi Helen, thanks for the quick response

--- In, Helen Borrie <helebor@...> wrote:
> >As an example, I connected via IBExpert, and after a really slow
> >startup if I dbl-click a table to bring up its details, this takes 20
> >seconds.
> "A table" isn't a table. It's the set returned from SELECT * FROM
ATABLE. How "fast" it is depends on how the client app is delivering
the set to you, i.e., does it fetch the entire set before showing you
anything? or does it fetch a "screenful" of records at a time?

Sorry, no I meant after starting IBExpert (default setup) you get the
treeview on the LHS; after expanding the tables node and
double-clicking a table, IBExpert displays (on the RHS) the Fields
(metadata view). But I'm pretty sure all the other tabs (constraints,
indices, data, etc) are not re-loaded at this time. After
double-clicking a table, it takes about 20 seconds.

> Hmm, there is no such thing as "GDS DB protocol". All packets are
TCP packets. gds_db is the symbolic name for the port on which the
Firebird server is listening. If you haven't specifically configured
it otherwise, the port is 3050.

Yes true, Wireshark is probably just recognising 3050 and displaying
'GDS DB' to be nice ;-)

> In short, you're barking up the wrong tree if you think that somehow
a "table" should magically appear on your screen in a single round
trip. If we are sensible, we don't design client applications that
pull entire "tables" across the wire. We think in "smallest possible
sets" and never do SELECT * queries - unless our app is a tool like
IBExpert, natch!

Ok yes I don't expect IBExpert to get everything in one step, but the
large number of round trips (insignificant on an internal network) are
killing our performance. (Would be nice if IBExpert could ask for more
of what it needs up-front, but who am I to know just what it needs to
do to display the info! ;-) )

> There's nothing "switched on" in the default configuration that is
designed to hobble anything. Have you examined your TCP/IP settings
to see whether your server defaults have been hobbled?

If doing things on the server itself it performs well as expected.
Actually we're in NZ, our server in Germany... but I just had a remote
session on one of our UK machines, connected in to the server... and
it's "lan speed", no detectable delay, the round-trips insignificant.
Both are in data centers and ping time between those two is <1ms...
it's the connection from NZ to Germany that is 300ms.

> It would be useful to mention whether you are running Classic or


> Fb 2's rejigged wire protocol does reduce the round trips under some
conditions, it's true, but it's no magic wand.

Yep I figured that... 50% improvement is great but when it's so slow
to start with, not the answer.

> >[When we run our main application, normal startup (includes a lot of
> >database I/O) with a local database is about 10 seconds,
> That's still far too long...."database I/O" can mean lots of
things....what does your app actually do at startup?

We cache a lot of data (in java hashtables for speed and reduced
network traffic) for example 'read-only' data, dozens of settings, and
a dictionary (21k words). Actually, the dictionary load is only about
1 second from a windows FB database, and from an intel 10.4 mac about
12 seconds(!)... but under 10.5, it's as fast as windows... so it's
great that Apple are getting osx up to speed!

> >but with the remote database, 20 minutes!!]
> And that's simply ridiculous. Something in your routing is causing
this...assuming of course that your remote client is a LAN node and
not an Internet connection...

It is ridiculous but it's partly due to the 100's of queries we run on
startup. On a lan this is an insignificant issue, but for a slow
external database......

Thanks, Phil