• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Looking for a good design for performance

 
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 .
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 .
 
Ranch Hand
Posts: 213
Eclipse IDE Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am using Oracle 10g
 
Richard Golebiowski
Ranch Hand
Posts: 213
Eclipse IDE Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 608
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!

reply
    Bookmark Topic Watch Topic
  • New Topic