At 11:56 AM 14/10/2007, you wrote:

>Please, what to use to collect related data from several tables into a
>dataset component in a client application ? Possibilities I can see are
>1 either use a dataset component with DSQL select statement and add
>lookup fields to a dataset object for data from related tables,
>2 or use DSQL select with join(s) to get resulting rowset with all
>needed columns at once
>3 or is it better to use selectable stored procedure or view which
>join the tables and give the resulting rowset ?
>
>It seems to me that procedures and views have advatages over DSQL
>approach but I don't have experiences. What about performance,
>variable sorting of the result set ?

The answer is that all are ways of retrieving data and each one has
advantages and disadvantages, depending on the size, shape and other
characteristics of the data you want to retrieve for the specific task.

From the POV of performance, if your set not too complex and can be
retrieved directly from the tables via a DSQL statement, then that is
your best choice. Dynamic sets retrieved from well-designed tables
with well-designed indexes using efficient SQL are the most
resource-efficient task for the optimiser and for the database
engine. Good indexes can in some cases improve retrieval efficiency
while bad ones nearly always ruin it. A view can be as efficient as
the underlying DSQL statement if you design it that way. Both views
and tables return sets that many DAC layers can treat as though they
were "updateable".

Stored procedures (and their close relative, triggers) are not a
singular thing that anyone could describe as "best", "worst" or even
"indifferent". In the area of retrieval you have what we call
"selectable stored procedures", which are SPs specifically designed
to return sets of data to the client by way of a SELECT
statement. The down-side of these is that they return rows of data
that does not exist. Some DACs can let the client side do wonderful
tricks with such sets and fool users into thinking they are looking
at real data. On the whole, that's OK, and a useful device for the
programmer to have up his sleeve for extracting otherwise troublesome
sets - provided the programmer isn't similarly deluded. :-)

The real power of stored procedures is with executable procedures -
the ones you invoke using EXECUTE PROCEDURE - in their ability to
perform complex processing at the server side in response to an
apparently simple request from the client side. Since they give you
the ability to ensure that *exactly the same processing* occurs,
regardless of what client environment the request came from, they are
the insurance you can build into the database to protect consistency.

"Lookup", from the point of view of your application design, is a
device inherited with Delphi's history as a back-end for Paradox and
dBase (desktop databases). "Live lookups" such as the VCL methods
implement belong to desktop databases of another age and should stay there.

In client/server, in moderation, the idea of "lookup sets" does have
a place. We can instantiate and link such sets to our dynamic sets
as a convenient way to supply our user with meaning for the various
paradigms of static control codes we provide in our design - such as
days of the week, account groupings and all sort of other things we
treat as "types". This kind of relationship is logically different
from the hierarchical or familial relationships we implement in
applications as "master-detail" or "parent-child". The more
sophisticated DACs provide ways to do very useful things with lookup sets.

>If it matters, I am using Delphi with FB2 and trying DAC libraries
>like FIBPlus, IBDAC and IBObjects.

It matters.

One of the quintessential things about writing Firebird apps is to
understand and make use of substitutable parameters. Most DAC suites
for Delphi depend on effective use of them. Don't be tempted to use
a "table" style of DAC: if it is available, it is just for
compatibility with the creaky old VCL. Its usefulness (if any) is
strictly limited to providing the data set for a very small control table.

I should remind you at this point that this is NOT a Delphi
list. All of the Firebird-capable DAC suites that are worth using
have their own user forums. Each suite is different from all the
others, both in capability and in the way the Firebird API is
implemented, so be prepared to ask the right questions in the right places.

^ heLen