[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3374) Subselect fetching fetches all elements in link table instead only needed
Alexander V. Zinin (JIRA)
noreply at atlassian.com
Mon Jul 7 18:41:38 EDT 2008
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_30588 ]
Alexander V. Zinin commented on HHH-3374:
-----------------------------------------
Create database
createuser -e -P hibernate_test -U postgres
createdb -E utf-8 -O hibernate_test hibernate_test -U postgres
Generate Schema:
create table Message (
id bigserial not null,
primary key (id)
);
create table Tag (
id bigserial not null,
primary key (id)
);
create table message_tag (
message_id int8 not null,
tag_id int8 not null,
primary key (message_id, tag_id)
);
alter table message_tag
add constraint FKCBFA93624540A685
foreign key (message_id)
references Message;
alter table message_tag
add constraint FKCBFA9362B74EE725
foreign key (tag_id)
references Tag;
Add test data(this is important):
insert into message(id) VALUES (nextval('message_id_seq'::regclass));
insert into tag(id) VALUES (nextval('tag_id_seq'::regclass));
insert into message_tag(message_id, tag_id) values(1, 1);
And run Main class, you can see query:
2008-07-08 02:41:12,059 DEBUG org.hibernate.jdbc.AbstractBatcher :401 -
select
this_.id as id0_0_
from
Message this_
2008-07-08 02:41:12,121 DEBUG org.hibernate.jdbc.AbstractBatcher :401 -
select
tags0_.message_id as message1_1_,
tags0_.tag_id as tag2_1_,
tag1_.id as id1_0_
from
message_tag tags0_
left outer join
Tag tag1_
on tags0_.tag_id=tag1_.id
where
tags0_.message_id in (
select
this_.id
from
Message this_
)
> Subselect fetching fetches all elements in link table instead only needed
> -------------------------------------------------------------------------
>
> Key: HHH-3374
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3374
> Project: Hibernate3
> Issue Type: Bug
> Components: core
> Affects Versions: 3.2.6
> Reporter: Alexander V. Zinin
> Attachments: hibernate_bug.rar
>
>
> I've some entity with many-to-many collection inside. Example:
> public class Message implements Serializable {
> @ManyToMany(fetch = FetchType.LAZY)
> @JoinTable(name = "message_has_file_attachment", joinColumns = {@JoinColumn(name = "message_id")},
> inverseJoinColumns = {@JoinColumn(name = "file_attachment_id")})
> @Fetch(value = FetchMode.SUBSELECT)
> public Set<MessageImageFileAttachment> getFileAttachments() {
> return fileAttachments;
> }
> }
> I want to fetch this collection for items I've fetched. I do:
> DetachedCriteria criteria = DetachedCriteria.forClass(messageClass);
> criteria.setFetchMode("fileAttachments", FetchMode.SELECT);
> List list = getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults);
> if (list.size() > 0) {
> Message message = (Message) list.get(0);
> Hibernate.initialize(message.getFileAttachments());
> }
> And hibernate generates subquery like this:
> select
> fileattach0_.message_id as message1_1_,
> fileattach0_.file_attachment_id as file2_1_,
> messageima1_.id as id12_0_,
> messageima1_.creationDate as creation3_12_0_,
> messageima1_.hostname as hostname12_0_,
> messageima1_.name as name12_0_,
> messageima1_.path as path12_0_,
> messageima1_.submited as submited12_0_,
> messageima1_.description as descript8_12_0_,
> messageima1_.hasSmallImage as hasSmall9_12_0_,
> messageima1_.height as height12_0_,
> messageima1_.smallHeight as smallHe11_12_0_,
> messageima1_.smallWidth as smallWidth12_0_,
> messageima1_.tinyHeight as tinyHeight12_0_,
> messageima1_.tinyWidth as tinyWidth12_0_,
> messageima1_.width as width12_0_
> from
> message_has_file_attachment fileattach0_
> left outer join
> file_attachment messageima1_
> on fileattach0_.file_attachment_id=messageima1_.id
> where
> fileattach0_.message_id in (
> select
> this_.id
> from
> message this_
> inner join
> a_user user2_
> on this_.user_id=user2_.id
> left outer join
> file_attachment articlemes3_
> on this_.image_id=articlemes3_.id
> )
> So it fetched ALL rows in table ! And what happens if there will be a millions rows ?
> http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#performance
> Subselect fetching - a second SELECT is used to retrieve the associated collections for all entities retrieved in a previous query or fetch. Unless you explicitly disable lazy fetching by specifying lazy="false", this second select will only be executed when you actually access the association.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list