CODEWITHSUNDEEP
mysqlhibernate
Jiunarayan
Jiunarayan

Reputation: 39

Fetching data Performace from Hibernate/MySQL

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.

Upvotes: 0

Views: 230

Answers (3)

Jiunarayan
Jiunarayan

Reputation: 39

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 ?

Upvotes: 0

Jiunarayan
Jiunarayan

Reputation: 39

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?

Upvotes: 0

esmoreno
esmoreno

Reputation: 666

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

Upvotes: 1

Related Questions