[hibernate-issues] [Hibernate-JIRA] Commented: (HSEARCH-704) Loading objects leads to 1+n queries when JPA's @Id != @DocumentId property

[Emmanuel Bernard (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HSEARCH-704?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=40103#action_40103 ] 

Emmanuel Bernard commented on HSEARCH-704:
------------------------------------------

Attaching the discussion between Hardy and me on the subject


11:14
emmanuel
ok the crux of the issue is about entities that have their @DocumentId which are not the JPa's @Id
hardy: if you look at returnAlreadyLoadedObjectsInCorrectOrder

11:15
hardy
i see the FIXME

11:15
emmanuel
the idea is that the objects do ahve been loaded
and we simply use the HashMap nature of Hibernate to retrieve them
But executeLoad does not do that int he case where @Id != @DocumentId
do you remember this code?

11:16
hardy
it uses a Criteria

11:16
emmanuel
right

11:16
hardy
well, I remember partly. It was quite some time ago

11:17
emmanuel
I think we should try and find a better approach somehow at some point

11:17
hardy
for the @Id != @DocumentId case you mean?

11:17
emmanuel
Maybe the first criteria query loads the object and project the docmentIdProperty and the entity

11:17
hardy
or the whole apporach?

11:17
emmanuel
and we keep this aroud
yes just for the @Id != @DocumentId
Today we literally do 1+ n queries 
It's not super urgent but we cna't really say that we support this use case until we are somewhat close performance wise. Today it's a bit misleading and I fear people will learn it the hard way

11:20
hardy
makes sense. we need to address this

11:20
emmanuel
anyways, I wanted to know if you had an idea at the time on how to improve that

11:21
hardy
not at the time
what do you mean with "Maybe the first criteria query loads the object and project the docmentIdProperty and the entity"

11:22
emmanuel
The key is that we want the object by documentId value
if we do have that we don't have to do the criteria query again just to order the objects
so imagine it's a user with id and a ssn
the ssn is the @DocumentId
if the first criteria query returns rows that looks like 
{ ssn1, person1 }
we have the data in memory
and we don't need the other criteria queries
makes sense?

11:24
hardy
i see where you are going with that
an other thing we could try is to tackle the 1 + n problem, by not loading each entity with a separate query

11:26
emmanuel
hardy: that's what my proposal aims at

11:26
hardy
right

11:26
emmanuel
the first query is a criteria query that does where 'ssn in (:listOfSSns)'
the n other queries are ssn = :ssn
but if we have the ssn, entity tuple in memory from the first query
we eliminate the n other queries altogether

> Loading objects leads to 1+n queries when JPA's @Id != @DocumentId property
> ---------------------------------------------------------------------------
>
>                 Key: HSEARCH-704
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HSEARCH-704
>             Project: Hibernate Search
>          Issue Type: Improvement
>          Components: query
>            Reporter: Emmanuel Bernard
>             Fix For: 3.4.0
>
>
> Today we load the objects once using a Criteria query.
> And to return the object by relevance, we call ObjectLoaderHelper.returnAlreadyLoadedObjectsInCorrectOrder
> which in the @DocumentId != @Id case, runs one criteria query per object again.
> That leads to 1+n queries which is not acceptable.
> To work around that we could (only in the @DocumentId != @Id) case:
>  - run the first criteria query so that it returns a List of tuples { documentIdProperty, entity }
>  - keep this List around 
>  - when loading entities ber relevant order, use this list to fetch the data from memory instead of this additional n Criteria queries)

-- 
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