• 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
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Pattern to retrieve data from multiple tables

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I have a workflow within my app which requires me to retrieve data from 7 tables. This operation will be done very frequently so I am hesitant to do a 7 table join as a dynamic query or a view.

I am looking for patterns/best practices on retrieving the data from the 7 tables with minimum resource usage and locking.

Any guidance/pointers will be greatly appreciated.

Thanks.
Sushma
 
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why are you hesitant to use a 7 table join?
 
Sushma Lakshmi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Ilja for responding.
Since this operation and consequently query is going to be executed very frequently (thousands of times every second), I am concerned that it might tax the database engine to breakdown . Fyi, most of the joins in the query are outer joins.

I am planning on doing some benchmarking today to check out the point of failure.
 
Ilja Preuss
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, if you need to the data from 7 tables, somewhere the join needs to be made. I'd expect a database to be the right tool for the job, as it is optimized for exactly this kind of work.

You could think about using a PreparedStatement, introduce indices or implement some caching if performance is a problem. But you certainly should first measure whether you actually have a problem...
 
Sushma Lakshmi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Ilja. Btw, can you recommend any good caching API/s.

I will be posting the results of my benchmark tests. However, I think it might take me a couple of days to get the tests setup and completed.
 
Ilja Preuss
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can't comment on caching APIs, sorry.

For benchmarking sql statements, take a look at p6spy.
 
Author and all-around good cowpoke
Posts: 13078
6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The open source WhirlyCache project looks good to me.

These 7 different tables - how often does the data change in each?

Bill
 
Sushma Lakshmi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Bill,

The data is inserted every second into the 7 tables. There will be more inserts and selects on these tables than updates.

Thanks.
 
Anderson gave himself the promotion. So I gave myself this tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic