Looking for a good design for performance

[Monoj Roy]

I have a requirement to fetch data from database .

Persion: ID, Col1,Col2,Col3
Address: ID, ID2 ,Col5,Col6
Contact: ID, ID3,Col8,Col9
ID is the primart key for Persion
ID2 is the primart key for Address
ID3 is the primart key for Contact

The relation of the tables are as follows
Persion-Address: (one to many)
Persion-Contact(one to many)

Requirement is I will be given values for Col9,Col3,Col5 and I have to fetch the persion Object
I am thinking for following design
select ID from Persion
for each ID
seclect * from Address and populate address Object
seclect * from Contact and populate contact Object
end of loop
construct the persion Object and return


Is there any other beter way to do this because in my database there are more tables and if I go by for loop it will decrease the performance ,So looking for a beter way of design .

[Jeanne Boyarsky]

Why are you not planning to use a SQL join? The database is going to be able to optimize that better than a loop.

[Monoj Roy]

I need to fetch the persion data along with all of its address and all of its contacts ,and this information is required for all the persion .

If I join all the tables then how do I separate all the address and contact information for all the persion ?

because at the end I need to create persion object and array of persion Object will be returned to consuming application .

[Richard Golebiowski]

It depends on how you are doing the database access. If you use something like Ibatis, it can create a collection of the person objects for you.

[Monoj Roy]

I am using Oracle 10g

[Richard Golebiowski]

iBATIS is a persistence framework which automates the mapping between SQL databases and objects in Java, .NET, and Ruby on Rails. It can be used with Oracle databases.

[Ireneusz Kordal]

Hi,
you may consider using cursor expressions.
This is Oracle feature that enable to embed results of nested subquery in the resultset of another (main) query.
With cursor expressions your query might look like this:

SELECT ID, Col1, col2, ......
            CURSOR(
                 SELECT id, id2, col5, ....
                 FROM Address A
                 WHERE A.id = P.id AND .......
            ),
            CURSOR(
                 SELECT id, id3, col8, .....
                 FROM Contact C
                 WHERE C.id =P.id AND .....
            )
FROM Persion P

On client side (JDBC) you can retrieve cursor expression as a resultset - and process this resultset like any other resultset

     PreparedStatement pr = connection.prepareStatement( main_query );
     ResultSet rs = pr.executeQuery();
     while( rs.next() ){
          String fileld1 = rs.getString(1); // get value of regular column
          .....
          // retrieve resultSet of first cursor expression - address
          ResultSet embedCursor = ((OracleResultSet)res).getCursor(4);
          while( embedCursor.next(){
               ..... process resultset of embed subquery - adresses
                    String col5 = embedCursor.getString(2);
                .... etc.
           }
           embedCursor.close(); // must close embed resultset !!!!

           //here code similar as above to process second cursor expression for contacts
           ResultSet embedCursor1 = ((OracleResultSet)res).getCursor(5);
           ........
     }
     rs.close();

The advantage is that only one query is submitted to the server instead of two or more unconnected queries that retrieves separately data from individual tables.
This query scans all necessary tables only once and it can be optimized more effectively by DB than two/more separate SQL.
There is however a disadvantage of the above solution - if the main table contains a lot of rows, there is a huge number of cursors created/opened/closed
and this could sometimes lead to problems.
This can be avoided if we create our query in this way:

WITH pers AS (
    SELECT id, col1, col2 .....
    FROM persion
    WHERE ..........
),
Addr AS (
    SELECT id, id2, col5 .....
    FROM address A
    WHERE A.ID IN ( SELECT id FROM pers )
               AND .........
),
Cont AS (
    SELECT id, id3, col8 ......
    FROM contact C
    WHERE C.ID IN  ( SELECT id FROM pers )
               AND .........
)
SELECT         CURSOR(  SELECT * FROM pers ),
               CURSOR(  SELECT * FROM addr ),
               CURSOR(  SELECT * FROM cont )
FROM dual

Here we created only 4 cursors for the whole query - 3 within the query and one for JDBC prepared statement..
WITH clause first runs query on Persons table, save results in memory and reuse these results in subsequent queries.
But in this case contacts and addresses are not embed in individual person data, and all three tables are retrieved as 'flat' tables
so we must populate data on client side
- first load persons to a map (ID is key) , then read addresses and assign them to persons looking at ID value,
and the same must be done with data from contacts.

Hope these help.

[Saifuddin Merchant]

Why not use a simple join as Jeanne suggest. The other solution would classified as 'complicated' to me!

sqlQuery = "select * from Person, Address, Contact where Person.id = Address.id and Person.id = Contact.id
resultset  = execute (sqlQuery )

For each resultset 
  Create a Person Object
End for

To the OP I would suggest looking at SQL Joins (this is specifically same a inner join) in case you don't understand how this works!