nested jdbc (select) queries

[koluwa]

Hi all,

I have a method in my data access layer, which retrieves a huge chunk of
data from an oracle 8i server. My problem is, this method has 3 levels of
nested (Select) queries. After executing the first query, I have to run
another query for each row of that ResultSet (using some of the values
extracted from the outermost query), and from the ResultSet of the second
query, I have to make another select query. All these queries are done on 3
different tables (currently there are no joins in the SQL).

This seems to be a huge performance issue (and poor design) since there will
be thousands of DB queries when the method returns. Does anyone know of a
solution for minimizing the amount of DB calls made from this method? Can
this be minimized with the use of java Collections? SQL joins?

Thanks in advance,
koluwa

 

[Bjorn Abelli]

...

I have a method in my data access layer, which retrieves
a huge chunk of data from an oracle 8i server. My problem
is, this method has 3 levels of nested (Select) queries.
[snip]

All these queries are done on 3 different tables
(currently there are no joins in the SQL).
[snip]

Does anyone know of a solution for minimizing the
amount of DB calls made from this method?

Can this be minimized with the use of java
Collections? SQL joins?

As you're using Oracle there seems to be at least three obvious solutions
that comes to mind:

1. Combine the SQL-queries to a single query

Even if there are "no explicit SQL-joins" (I interpret that statement as
that you mean that there are no Foreign Key-Primary Key relations created on
the tables in your queries), you *still* can join the tables as Oracle
allows "value-based" joining even if no FK-PK relations exist.

Though, the performance should most probably improve if you actually create
those relations, as well...

2. Create views

By creating "views" in the database, corresponding to the above mentioned
joins, you can simplify how you make the query from Java in the first place.

3. Create a stored procedure

If the results from each query in some way must be "processed" before you
can use the result into the next query, this "processing" can be placed on
the server instead of in the Java-application. This would minimize the calls
to the server to possibly just one.

4. A combination of the above.

If you together with your DBA walk through what the steps of retrieving and
processing of data really are, you most probably will end up with the use of
a combination of all above, as each has its benefits.

my 2c

// Bjorn A