It's not a secret anymore!
The moose likes Performance and the fly likes Looking for a good design for performance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Building Microservices this week in the Design forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "Looking for a good design for performance" Watch "Looking for a good design for performance" New topic

Looking for a good design for performance

Monoj Roy
Ranch Hand

Joined: Oct 10, 2007
Posts: 98
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
author & internet detective

Joined: May 26, 2003
Posts: 32507

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

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Monoj Roy
Ranch Hand

Joined: Oct 10, 2007
Posts: 98
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
Ranch Hand

Joined: May 05, 2010
Posts: 213

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
Ranch Hand

Joined: Oct 10, 2007
Posts: 98
I am using Oracle 10g
Richard Golebiowski
Ranch Hand

Joined: May 05, 2010
Posts: 213

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
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
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:

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

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:

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
Ranch Hand

Joined: Feb 08, 2009
Posts: 607

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

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!

Cheers - Sam.
Twisters - The new age Java Quiz || My Blog
I agree. Here's the link:
subject: Looking for a good design for performance
It's not a secret anymore!