Subject Re: help needed. browsing slows down after a days work
Author mmenaz
--- In IBObjects@y..., "Artur Anjos" <arsoft@n...> wrote:
> I will try to give my 10 cents here. I think that this text is at
some point mixing things up. So, here it goes:
> select * from MyHugeDatabaseTable
> The use of a expression like this is very acceptable, and it is not
related to the number of rows that will be returned. It depends of
the number of FIELDS. Don't care if you use it or not in a Huge or
Small Table.
> The part that is related to the number of rows is related to the
'where' clause. That's the way you restrict the number of rows
returned by the server.

In the question, it appeared to me reasonable clear that very often
in his program there is NO where caluse at all. Why do you guest that
there is one?
In the question he says also:
"the query for the grid loads perhaps 6000 records"
a little reducing "where" indeed...

> With IBO you can do better: you can define the number of rows that
will be returned from a 100000000000 records table. You can play with
this turning 'AutoFetchAll' to False, and playing with MaxRows and
MaxTicks. Read the help file.

You must consider that MaxRows default is 0 (no limit), and even if
you set it to a different value, a query that fetches a huge amount
of data just to display them into a grid in a form that stais open
all day long is a non-sense.

> I think Marco is wrong in this explanation (sorry, Marco, you spend
a lot of time on this explanation), but I think the problem is
transaction related:

Isn't the first thing I said in my reply?
(first sentence...)

>If you open a transaction and keep the transaction open for a long
time, thats a problem that can slow down IB after some time.

It's not clear if the problem is in all the workstation connected to
the server, of in each workstation after it's on for that time (I
mean, I have 5 workstation slow, then I turn on the 6°... is it

> So, this is my suggestion: see what you are doing with the
transactions. My guess is that you are openning the transaction and
keep it open until the user close the form. Try to keep it simple
like 'open transaction, do your changes, close transaction'. Post it
or Roll back, but do something.

> I hope it helps,
> Artur Anjos
> ----- Original Message -----
> From: mmenaz
> To: IBObjects@y...
> Sent: Tuesday, December 11, 2001 6:43 PM
> Subject: [IBO] Re: help needed. browsing slows down after a days
> I've not huge database, and I'm not an expert of performance
issues, but I've read a lot of times in this newsgroup about the
problem, and the performance penalty, of leaving transaction open for
a long time.
> First of all, C/S programming is based, for interactive use, of
SMALL amounts of data, so you should avoid having something like
> select * from MyHugeDatabase
> Since it's very unlikely that the user will scroll the grid to
see all the 100,000 rows inside.
> If he needs to control last inserted/modified records, add a
timestamp to the table definition, updated by triggers, and in a
regular basis, or at user request, reperform the query
> select * from MyHugeDatabase where WHEN_MODIFIED <= '12/05/01
> If the browsing is read only, set the dataset to read only too
(and, maybe, have another dataset chained to the actual record (using
master/detail mechanism) for some update).
> Consider that IBO, for a select * from MyHugeDatabase, thinks
that you really need all the rows from the table, since it's what you
have requested. It's wonderful "under the hood" features start
fetching all the rows in the background, so your client will be
filled with all the rows as time passes. So bad for you!
> Just a contribution :)
> Regards
> Marco Menardi
> --- In IBObjects@y..., "DennisFantoni" <df@i...> wrote:
> > I've gotten this weird bugreport from my customer, and they
> > confirmed the problem is not only one computer, but all of them.
> >
> > they have an application with an interbase6 server and abt. 20
> > workstations.
> >
> > whe a workstation is on, it shows a ib_grid that connects to a
> > typically something like select * from table (returning lots
of rows)
> >
> > The application works fine and browsing is quite fast, all
> >
> > But after 5-6 hours work, brosing becomes really really slow.
> > 20 times slower than at the beginning.
> >
> > Does anyone have any idea what this could be? Usually my
> > start being slow, and stays so ;-) ( or starts being fast and
> > so..).
> >
> > Is there something in ibo or interbase that might go slower
over time?
> >
> > the query for the grid loads perhaps 6000 records.
> >
> > I would like some pointers as to where to look for problems. I
> > used ib_grids before - the other ibobject applications that i
> > were not using db-aware controls. This application was not
> > by myself, but the programmer seems to have known the database
> > objects quite well. Still, he migt've done something wrong that
> > causes things to slow down.
> >
> > My own idea of attacking the problem right now, is to somehow
try to
> > get the slowing down to occour at my own computer, then to see
> > there's any local memory leaks.
> >
> > Dennis
> [Non-text portions of this message have been removed]