Pattern to retrieve data from multiple tables

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

Why are you hesitant to use a 7 table join?

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.

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...

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.

Can't comment on caching APIs, sorry.

For benchmarking sql statements, take a look at p6spy.

The open source WhirlyCache project looks good to me.

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

Bill

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.