Recently I had some problem in performance of my query. The thing is described here: poor Hibernate select performance comparing to running directly - how debug?
After long time of struggling, I've finally discovered that the query with select prefix like:
select sth.* from Something as sth...
Is 300x times slower then query started this way:
select * from Something as sth..
Could somebody help me, and asnwer why is that so? Some external documents on this would be really useful.
The table used for testing was:
SALES_UNIT table contains some basic info abot sales unit node such as name and etc. The only association is to table SALES_UNIT_TYPE, as ManyToOne. The primary key is ID and field VALID_FROM_DTTM which is date.
SALES_UNIT_RELATION contains relation PARENT-CHILD between sales unit nodes. Consists of SALES_UNIT_PARENT_ID, SALES_UNIT_CHILD_ID and VALID_TO_DTTM/VALID_FROM_DTTM. No association with any tables. The PK here is ..PARENT_ID, ..CHILD_ID and VALID_FROM_DTTM
The actual query I've done was:
select s.* from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) where r.sales_unit_child_id is null
select * from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) where r.sales_unit_child_id is null
Same query, both uses left join and only difference is with select.
