Subject RE: [firebird-support] Re: Re: Getting n number of rows from a table ...
Author Alan McDonald
I have a further question for optimizing the database
interaction. I need
to execute a query that fetches data from about 5-7 different
tables. I
could either create one big query that uses numerous JOINs and
other things,
and gives back one single resultset. Alternately I could break
up the task
into two or more queries and process them separately. For e.g.
getting the resultset for one query and then running another
query for this
resultset. Then I could either implement this setup using a
View or a
Stored Procedure or directly fire the query from my application.
approach would give me the best results in terms of speed as
well as

Thanks and Regards,

Anand S. Kashelkar

UNIONS are generally very fast. If you can, you are always advised to use
the server to do the compilation of datasets - not the client.
That said, if you want to make this dataset updatable, then one select
statement (with UNIONS) would be sligthly more difficult to make
"automatically" updatable but certainly not impossible. It all depends on
what connection components you are using.
If it's a straight browse of the data, then there's no question about one
UNION being the better way. The server does it all, fast, delivers it in one
go to the client, less network traffic, and far less client CPU cycles
involved in compiling the final dataset.
Some of this could also be optimised in your overall database design as