At 08:40 AM 30/06/2003 +1000, Peter McLeod wrote:

>Hello Helen,
>
>-----Original Message-----
>From: Helen Borrie [mailto:helebor@...]
>Sent: Friday 27 June 2003 6:16 PM
>To: IBObjects@yahoogroups.com
>Subject: RE: [IBO] IBO seems slower for some things compared with the
>BDE
>
>
>At 02:42 PM 27/06/2003 +1000, Peter McLeod wrote:
>
>This is by no means a good rule for optimal performance with IB unless a
>dataset is wanted only once in the session. The problem with this
>technique is that you waste both server resources and increase network
>traffic by preparing the statement every time it is used.
>
>Actually that depends on how you create and dispose of the objects that
>you are using. You can still prepare and not do it once every time it is
>used. It would seem from your comment above that you believe that the
>object is created used and destroyed immediately. This is done
>occassionally for one off things, but not for others.

Good. My comment was prompted by your statement:

> Most datasets are created dynamically and then destroyed when fished

[finished?], in order to reduce network traffic and conserve system
resources.

It's hard to marry that statement with your response...

> >Where ever possible I try and use appropriate client server
> >design techniques (For instance refer to my article on Delphi 3000. Improve
> >Interbase Client Server Performance.


>Having read your article, I would guess that there are probably a lot of
>things to look at. Perhaps a good starting place would be the TechInfo
>papers, esp. those on transactions. If you *believe* the stuff in your
>article about IB indexes, then I'd suspect that you have some problem
>indexing to deal with. If you are using Autocommit, then you need to look
>at its implications for database performance; and you need to get hold of
>some better "facts" about garbage collection.
>
>Having had a quick look across the net after reading your response I
>rechecked
>information about garbage collection and didn't find anything that seemed
>contradictory to my views. So maybe you have read something

Lots. And researched it lots. And written about it lots.
The crucial thing for you to look at regarding performance and garbage
collection will be the topics around "keeping the Oldest Interesting
Transaction moving forward".

>that I havent (if so please e-mail me the artivle off list.

You will get a lot of the relevant information from the TechInfo sheets and
possibly not from many other free documentation. It was never
well-documented in the InterBase manuals and seems to be something that the
official IB support personnel are unacquainted with. Charlie Caro (IB
developer, not support personnel) has posted on the topic in the past -
browse his postings in the Mers archives.

>However I note that even some
>of the documents on ibphoenix seem to state the same thing
>http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_tip_perf

There are a few "generically good" ideas in this paper. However, it is
very ancient and tends to reflect current wisdom as seen through the eyes
of IB support personnel around 1996-7 when IB 5 first came out. Their
experience as IB users was limited to writing tiddly little BDE apps for
demos at Borland conferences. High up in the article is some very bad
advice for Windows users (it recommends using async writes which, on
Windows, as we well know, is a disaster waiting to happen...) The stuff on
indexes is very general and doesn't give any good advice about the way they
are used by the optimizer...and on and on...

>)As
>for indexes, this is from experience, I will check the technical articles,
>however if you have any specifically in mind then please e-mail me off list
>as this is an IBO list not Interbase (I just don't want to create an off the
>topic thread).

I made some notes as I was reading your article, which I will be happy to
email to you.

>Also, I think you would gain a benefit by rethinking your views about
>triggers and stored procedures. It's just not true that they cause
>bottlenecks on the server! Some people do avoid them because they want
>their applications to be portable to a number of different RDBMSs. They do
>that at the cost of performance and integrity. In fact, the importance of
>enforcing business rules in one and only one place wasn't mentioned in your
>article at all.
>
>Actually Helen, I didn't say that triggers and stored procedures were
>bottlenecks,
>in fact I said the exact opposite. Are you reading the correct
>article? In fact I
> even defended them when someone said that they were bottlenecks in the
> comments
>to the article.

To refresh your memory, this is what you said:

"Usually I find that the worst bottleneck to performance is non-optimised
SQL statements. In my experience this causes around 80% of all system
problems, and yes this is usually built into the Server as Stored
Procedures etc.

1. There are some tools to do this (such as EMS Interbase / Firebird
Manager) that allow you to step though stored procedures etc. Alternatively
a UDF that outputs a message dialog with debug information from your stored
procedure also works.

2. I agree [re point that powerful client machines should be utilised
instead of burdening the server with data processing----ARRRRGHHH!!!] , but
then it depends on your application. Not everything can be transported down
to the client, particularly where a large amount of data is involved.

3. It is an article on Optimising Interbase, and you tend to work with what
you have. I prefer to use Stored Procedures and triggers only when
necessary, but yes it is a valid point."

>Now I confess that I never mentioned about enforcing business rules
>in one place, but that was because this was an article about Interbase
>performance.

...and I argue strongly that there are no conditions under which it is wise
to compromise integrity for performance...I guess my main impression from
your article was that the crucial part that transaction management plays in
performance was simply not mentioned. Your confusion about the way indexes
are used was also very misguiding.

I do recommend that you join the firebird-support list and fill out your
ideas about performance optimisation (via smart queries, configuration
tweaking and optimal transaction management) by asking questions
there. Opinions there don't always agree, but you do tend to get some good
ideas to test for yourself. An added bonus is that people like you, who
are really keen to pass on your experience to others, stay around and
become gurus themselves as the really important pieces fall into place.

regards,
Helen