CODEWITHSUNDEEP
sqlhibernatehql
user510783
user510783

Reputation: 275

faster select statement using hibernate

In few article it is mentioned that we should not fetch all column of a table. We should avoid Select * from table1 and write Select name, age, phone from table1.

Q1) Does this mean while writing HQL, we should not write from Table1?

Q2) I heard that some company uses stored procedure in their java code to fetch data, instead of writing select statement. Is this technique really efficient?

Q3) I heard that for a single application. Sometimes many database are used instead of having single database. Why is that so? Is this technique a good one?

Upvotes: 2

Views: 736

Answers (2)

Bohemian
Bohemian

Reputation: 425318

Answer 1: Hibernate does not use select *: It translates select from table to select col1, col2, col3 ... from table1. Further, if you do not define a column to hibernate, it will not select it.

Answer 2: My recommendation is to avoid using stored procedures. They are rarely in fact faster than queries, especially for simple data retrieval. There are many good reasons not to use them, which include 1) not portable, 2) impossible to debug, 3) can't unit test, the list goes on

Answer 3: I would use one database and not split your data. It's too hard to manage and you are adding more components to your system for no gain (also most systems use one database, so it can't be that bad). Distributed databases however are a good idea.

Upvotes: 4

Piotr Kochański
Piotr Kochański

Reputation: 22692

The idea of using ORM is to work with Java object. So using from Table is ok, it gives you ready to work with objects but...

It may happen that you have large fields Blobs or Clobs, then mark them as lazyly loaded, so you will not have to keep them in Session cache, which costs memory.

It may also happen that you need to select 100 000 rows, then it does not make sense to use ORM mapping. You should fall back to SQL native queries (which is also nicely managed by Hibernate).

For small result sets (you can restrict it in your where clause) it is ok to use HQL, unless you have large BLOBs and CLOBs. Since the result set is small it is not so important if you select needed fields or not (obviously you can do this, but it may be unexpected by other team member, if some data is missing).

For large datasets HQL does not make sense.

Upvotes: 1

Related Questions