• Post Reply Bookmark Topic Watch Topic
  • New Topic

Pattern to retrieve data from multiple tables  RSS feed

 
Sushma Lakshmi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • 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
 
Ilja Preuss
author
Sheriff
Posts: 14112
  • Mark post as helpful
  • send pies
  • 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
  • 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
Sheriff
Posts: 14112
  • Mark post as helpful
  • send pies
  • 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
  • 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
Sheriff
Posts: 14112
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can't comment on caching APIs, sorry.

For benchmarking sql statements, take a look at p6spy.
 
William Brogden
Author and all-around good cowpoke
Rancher
Posts: 13078
6
  • Mark post as helpful
  • send pies
  • 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
  • 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.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!