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.
first
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.
Which
approach would give me the best results in terms of speed as
well as
maintenance?


Thanks and Regards,

Anand S. Kashelkar
Mumbai

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
well.
Alan