Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Looking for a good design for performance

 
Monoj Roy
Ranch Hand
Posts: 98
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Posts: 33697
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 98
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 213
Eclipse IDE Java Tomcat Server
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 98
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using Oracle 10g
 
Richard Golebiowski
Ranch Hand
Posts: 213
Eclipse IDE Java Tomcat Server
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 423
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


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
Posts: 607
Firefox Browser Java Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!

 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic