I'm trying views for the first time with Hibernate. So far, it's working pretty good. I created the view, created the mapping, and created a bean class to represent the view object. Now I can create HQL queries into the view just like any table. Great.

Except for one thing - performance. I understand that the first time I access the view during a session, the database has to build the view. My view currently contains 8942 rows. It's taking 1.8 seconds to query the view. Not great, but not terrible. The problem is that I need to do about a dozen queries on that view during the session and it's taking approximately the same amount of time for each one. From that observation, I conclude that the database is rebuilding the view for each query. I was hoping that I could pay the performance penalty of building the view on only the first access while the subsequent accesses would be quicker, but that's not how it's turned out.

My question: Is there something I can do at the Hibernate level to reuse the view during a session without having the DB rebuild the view on each query? Or is this something outside the scope of Hibernate that I will have to handle elsewhere? I'm hoping for a Hibernate solution that works across all databases so I can avoid special cases for each kind of database engine.

Hibernate version: 3.0.2

There is no "building of the view", you are mistaken on that point. A view is converted by the DBMS into an optimized select at execution time. (This may not be true of updatable views, but I've never bothered with them, so I can't say for sure)

1.8 seconds for selecting under 9000 rows is not good on any CPU faster than a 66MHz 486. Unless you're building 50 objects out of each row, maybe.

It's most likely that you are using table scans: you need to add appropriate indices to your tables. You need (well, should have) an index on each column that the view joins on (so if it joins tableA, column1 against tableB, column7, you need those two indicies), and an index on the combination of query columns (so if you "select * from view where column3 = ? and column10 is not null" then you should have an index on (column3 and column10)).

Obviously you don't need all the indices, and they can get quite large, so maybe you will choose to skip a few. But if you have an execution plan feature in the client you use to connect to your DBMS, use it to figure out where the lag in your query is coming from.

Okay, I'm sorry for not knowing the correct terminology, but that *is* what I meant when I said "build the view". I'm wondering why I would want the optimized select executed over and over again. Wouldn't it be better to execute the select which defines the view just once and then run the queries on the results of that? I mean, the select produces results, right? It seems like the results are being thrown away and recomputed each time I query into the view.

But maybe I'm wrong. Maybe it's like you suggested - indicies are missing which could speed up the queries. Maybe that will solve the whole problem. I will index those columns I use in the WHERE clause and see what happens. Thanks for your suggestion.

No, it's not what you meant. There's a subtlety that you're missing. The view doesn't get converted to a joined table, then have a select issued on it: the view modifies the incoming select statement. So if your view isand you issue the request "select * from view1 where column4 like = 'A%'", then inside your DBMS what actually happens isSo there's nothing about the view that's being regenerated each time, because the view is nothing except a way to modify an incoming select statement. The only way to optimize your view is to make that join more efficient, and to make any where clauses you later run on it more efficient.

If you want to cache the results of "select * from view1" in java, then search on those results yourself, you can do that. But you can't do it on the DB side, so there's nothing you can do to hibernate to make that happen.

aha! You're right, I was missing that. Very educational. Thanks.