Fetching data Performace from Hibernate/MySQL

Question

I got multiple tables where I have to join, subquery,pagination, grouping, ordering . Keeping hibernate limitation in mind, sometime native SQL is required and during this time hibernate cache is helpless. Also the data is stored in hibernate second level cache is not automatic, since its stored only when DB is accessed. So first time second level cache is empty.

My problem is I used native sql to get data with multiple joins and grouping,ordering, finally ending up in the performance issue.

My thoughts: I like sql VIEW to pull data with all those joins ,ordering , grouping. But the sql VIEW is like a normal select statement and executes every time on access. Is there any live result set as table where I can just say fetch data as select * from ONE_LIVE_RESULT_SET where condition. Is there any concept like LIVE_RESULT_SET IN sql world? Any comments.

esmoreno · Accepted Answer · 2013-10-17 14:06:57Z

1

Use a materialized view

Extract from Wikipedia: http://en.wikipedia.org/wiki/Materialized_view

A materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary based on aggregations of a table's data. Materialized views, which store data based on remote tables, are also known as snapshots. A snapshot can be redefined as a materialized view.

Example syntax to create a materialized view in Oracle:

CREATE MATERIALIZED VIEW MV_MY_VIEW REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1 AS SELECT * FROM ;

Regards

answered Oct 17, 2013 at 14:06

esmoreno

6665 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jiunarayan · Accepted Answer · 2013-10-18 05:26:17Z

0

But this MATERIALIZED VIEW is not a live data(sync up with table) but inorder to make it live data it has to be REFRESH. Here the question will be When to REFRESH OR during such refresh again one has to wait. Also frequent data changing is another use case to suffer. Is there any ways where the refresh can be done for specific row?

answered Oct 18, 2013 at 5:26

Jiunarayan

395 bronze badges

Comments

Jiunarayan · Accepted Answer · 2013-10-18 10:24:31Z

0

Any hibernate experts!!! Does HIBERNATE persist the data on multiple joins, complex joins? I have seen hibernate persisting second level cache session.get(id), but I am not sure about the hql or native sql having multiple/complex join. Is it possible to get from hibernate second level cache for multiple/comples joins ?

answered Oct 18, 2013 at 10:24

Jiunarayan

395 bronze badges

Collectives™ on Stack Overflow

Fetching data Performace from Hibernate/MySQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related