April 2023 - hibernate-issues - Jboss List Archives

[JIRA] (HHH-16427) Performance improvement for the @BatchSize

by Artem Gordienko (JIRA)

Artem Gordienko ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=600c501... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNjIxOTc5ZDgx... ) / Improvement ( https://hibernate.atlassian.net/browse/HHH-16427?atlOrigin=eyJpIjoiNjIxOT... ) HHH-16427 ( https://hibernate.atlassian.net/browse/HHH-16427?atlOrigin=eyJpIjoiNjIxOT... ) Performance improvement for the @BatchSize ( https://hibernate.atlassian.net/browse/HHH-16427?atlOrigin=eyJpIjoiNjIxOT... ) Change By: Artem Gordienko ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=600c501... ) When using the annotation {{@BatchSize(size = 10)}} for {{@OneToMany}} collection, we need to specify the size parameter, and if, when loading the collections of the passed ids, more than the specified size, then several queries will be executed. {noformat}@Entity @Table(name = "CLIENT") class ClientEntity( @Id @Column(name = "ID") var id: Long? = null, @OneToMany(mappedBy = "client") @BatchSize(size = 10) var accounts: Set<AccountEntity> = mutableSetOf() ){noformat} Select clients: {noformat}fun getAllBy(pageable: Pageable): Page<ClientEntity>{noformat} Result in logs: {code:sql}Hibernate: select * from client c offset ? rows fetch first ? rows only Hibernate: select * from account a where a.client_id in (?, ?, …) Hibernate: select * from account a where a.client_id in (?, ?, …){code} To improve performance, it would be better if additional queries were not created. For this, the best option would be if request with received ids returned all the required result. How about creating an annotation of similar functionality (example {{@AllLoadable}})without size parameter and the request could be like: {code:sql}select * from client c where c.id in (select unnest(array [1, 2, 3, 4])) // Works For example, works in postgresql for example {code} In some databases there is a limitation in the size of the in clause, passing an array as one parameter should help to the in clause should help to work around this limitation. [https://hibernate.atlassian.net/browse/HHH-1123|https://hibernate.atlassi...] ( https://hibernate.atlassian.net/browse/HHH-16427#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16427#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100221- sha1:d541bce )

1 year, 10 months

1
0
0 / 0

[JIRA] (HHH-16427) Performance improvement for the @BatchSize

by Artem Gordienko (JIRA)

Artem Gordienko ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=600c501... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiOTUwM2YwZTRm... ) / Improvement ( https://hibernate.atlassian.net/browse/HHH-16427?atlOrigin=eyJpIjoiOTUwM2... ) HHH-16427 ( https://hibernate.atlassian.net/browse/HHH-16427?atlOrigin=eyJpIjoiOTUwM2... ) Performance improvement for the @BatchSize ( https://hibernate.atlassian.net/browse/HHH-16427?atlOrigin=eyJpIjoiOTUwM2... ) Change By: Artem Gordienko ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=600c501... ) When using the annotation {{@BatchSize(size = 10)}} for {{@OneToMany}} collection , we need to specify the size parameter, and if, when loading the collections of the passed ids, more than the specified size, then several queries will be executed. {noformat}@Entity @Table(name = "CLIENT") class ClientEntity( @Id @Column(name = "ID") var id: Long? = null, @OneToMany(mappedBy = "client") @BatchSize(size = 10) var accounts: Set<AccountEntity> = mutableSetOf() ){noformat} Select clients: {noformat}fun getAllBy(pageable: Pageable): Page<ClientEntity>{noformat} Result in logs: {code:sql}Hibernate: select * from client c offset ? rows fetch first ? rows only Hibernate: select * from account a where a.client_id in (?, ?, …) Hibernate: select * from account a where a.client_id in (?, ?, …){code} To improve performance, it would be better if additional queries were not created. For this, the best option would be if additional requests request with received ids returned all the required result. How about creating an annotation of similar functionality (example {{ @AllLoadable }} ) that would not accept a without size parameter and the request could be like: {code:sql}select * from client c where c.id in (select unnest(array [1, 2, 3, 4])) // Works in postgresql for example {code} In some databases there is a limitation in the size of the in clause, passing an array as one parameter should help to the in clause should help to work around this limitation. [https://hibernate.atlassian.net/browse/HHH-1123|https://hibernate.atlassi...] ( https://hibernate.atlassian.net/browse/HHH-16427#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16427#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100221- sha1:d541bce )

1 year, 10 months

1
0
0 / 0

[JIRA] (HHH-16427) Performance improvement for the @BatchSize

by Artem Gordienko (JIRA)

Artem Gordienko ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=600c501... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiY2Q0ZmQ4ZTg5... ) / Improvement ( https://hibernate.atlassian.net/browse/HHH-16427?atlOrigin=eyJpIjoiY2Q0Zm... ) HHH-16427 ( https://hibernate.atlassian.net/browse/HHH-16427?atlOrigin=eyJpIjoiY2Q0Zm... ) Performance improvement for the @BatchSize ( https://hibernate.atlassian.net/browse/HHH-16427?atlOrigin=eyJpIjoiY2Q0Zm... ) Issue Type: Improvement Affects Versions: 6.2.0 Assignee: Unassigned Components: hibernate-core Created: 03/Apr/2023 11:44 AM Priority: Major Reporter: Artem Gordienko ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=600c501... ) When using the , we need to specify the size parameter, and if, when loading the collections of the passed ids, more than the specified size, then several queries will be executed. To improve performance, it would be better if additional queries were not created. For this, the best option would be if additional requests with received ids returned all the required result. How about creating an annotation of similar functionality (example @AllLoadable)that would not accept a size and the request could be like: select * from client c where c.id in ( select unnest ( array [1, 2, 3, 4])) ( https://hibernate.atlassian.net/browse/HHH-16427#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16427#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100221- sha1:d541bce )

1 year, 10 months