How to make hibernate query faster
up vote
0
down vote
favorite
I am currently working on a task to improve performance of an action and the slowest part of code is this hibernate query:
.createQuery( "SELECT distinct p FROM CSParcel p " +
" WHERE p.bag.destination.loop.line.changeover.barcode = :barcode AND p.created > :limit " +
" AND (p.bag.destination.loop.courierLoop.id = :courierId OR p.bag.destination.loop.line.courier.id = :courierId)" +
" AND p.latestAction.actionType <> :doruceni" + " AND p.id NOT IN " +
"(SELECT pa.parcel.id " + " FROM CSParcelAction pa " +
" WHERE pa.actionType = :predani AND pa.courier.id = :courierId AND pa.timestamp > :limit" +
")", CSParcel.class )
.setParameter( "barcode", changeoverBarcode.toUpperCase() )
.setParameter( "limit", Time.setTime( 0, 0, 0, cal.getTime() ) )
.setParameter( "courierId", courierId )
.setParameter( "predani", CSParcelActionType.CHANGEOVER )
.setParameter( "doruceni", CSParcelActionType.DELIVERY )
.getResultList();
are there any ways to make it run faster?
Thank you for every suggestion.
java performance hibernate
asked Nov 22 at 14:36
Sam
11
add a comment |
up vote
0
down vote
favorite
I am currently working on a task to improve performance of an action and the slowest part of code is this hibernate query:
.createQuery( "SELECT distinct p FROM CSParcel p " +
" WHERE p.bag.destination.loop.line.changeover.barcode = :barcode AND p.created > :limit " +
" AND (p.bag.destination.loop.courierLoop.id = :courierId OR p.bag.destination.loop.line.courier.id = :courierId)" +
" AND p.latestAction.actionType <> :doruceni" + " AND p.id NOT IN " +
"(SELECT pa.parcel.id " + " FROM CSParcelAction pa " +
" WHERE pa.actionType = :predani AND pa.courier.id = :courierId AND pa.timestamp > :limit" +
")", CSParcel.class )
.setParameter( "barcode", changeoverBarcode.toUpperCase() )
.setParameter( "limit", Time.setTime( 0, 0, 0, cal.getTime() ) )
.setParameter( "courierId", courierId )
.setParameter( "predani", CSParcelActionType.CHANGEOVER )
.setParameter( "doruceni", CSParcelActionType.DELIVERY )
.getResultList();
are there any ways to make it run faster?
Thank you for every suggestion.
java performance hibernate
asked Nov 22 at 14:36
Sam
11
Well, that depends on how "slow" it really is. Did you check the SQL that's generated (Hibernate will log the SQL query as DEBUG underorg.hibernate.SQL)? If not then you can take that query and directly execute it against your database (you'd need to add the parameters though) and add theexplaincommand to let the database tell you how that query is executed. This should provide some hints on where you could improve it, e.g. by adding some indices.
– Thomas
Nov 22 at 14:50
Thank you for your answer @Thomas , this query can take from 20sec. to 40sec. for almost 600 entities. I was adviced to use joins instead of subqueries. I think that going through so many parametersp.bag.destination.loop.line.changeover.barcode = :barcodeis slowing this down. Is there any way to get around this?
– Sam
Nov 22 at 15:09
Well,p.bag.destination.loop.line.changeover.barcodewould probably result in at least 5 joins but those seem to be to-one relations or otherwise Hibernate would complain. It's hard to tell what exactly is slowing down the query without knowing much more hence my suggestion to useexplainon the resulting SQL query.
– Thomas
Nov 22 at 15:28
1
A few suggestions though: 1) check how many results you'd get without thedistinct- we had a case where a couple of joins resulted in 1 million rows being returned for one (!) distinct entity. 2) check whether there are any sequential scans that could be sped up with an index 3) instead of thenot in <subquery>trynot exists <subquery>.
– Thomas
Nov 22 at 15:31
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am currently working on a task to improve performance of an action and the slowest part of code is this hibernate query:
.createQuery( "SELECT distinct p FROM CSParcel p " +
" WHERE p.bag.destination.loop.line.changeover.barcode = :barcode AND p.created > :limit " +
" AND (p.bag.destination.loop.courierLoop.id = :courierId OR p.bag.destination.loop.line.courier.id = :courierId)" +
" AND p.latestAction.actionType <> :doruceni" + " AND p.id NOT IN " +
"(SELECT pa.parcel.id " + " FROM CSParcelAction pa " +
" WHERE pa.actionType = :predani AND pa.courier.id = :courierId AND pa.timestamp > :limit" +
")", CSParcel.class )
.setParameter( "barcode", changeoverBarcode.toUpperCase() )
.setParameter( "limit", Time.setTime( 0, 0, 0, cal.getTime() ) )
.setParameter( "courierId", courierId )
.setParameter( "predani", CSParcelActionType.CHANGEOVER )
.setParameter( "doruceni", CSParcelActionType.DELIVERY )
.getResultList();
are there any ways to make it run faster?
Thank you for every suggestion.
java performance hibernate
asked Nov 22 at 14:36
Sam
11
I am currently working on a task to improve performance of an action and the slowest part of code is this hibernate query:
.createQuery( "SELECT distinct p FROM CSParcel p " +
" WHERE p.bag.destination.loop.line.changeover.barcode = :barcode AND p.created > :limit " +
" AND (p.bag.destination.loop.courierLoop.id = :courierId OR p.bag.destination.loop.line.courier.id = :courierId)" +
" AND p.latestAction.actionType <> :doruceni" + " AND p.id NOT IN " +
"(SELECT pa.parcel.id " + " FROM CSParcelAction pa " +
" WHERE pa.actionType = :predani AND pa.courier.id = :courierId AND pa.timestamp > :limit" +
")", CSParcel.class )
.setParameter( "barcode", changeoverBarcode.toUpperCase() )
.setParameter( "limit", Time.setTime( 0, 0, 0, cal.getTime() ) )
.setParameter( "courierId", courierId )
.setParameter( "predani", CSParcelActionType.CHANGEOVER )
.setParameter( "doruceni", CSParcelActionType.DELIVERY )
.getResultList();
are there any ways to make it run faster?
Thank you for every suggestion.
java performance hibernate
java performance hibernate
asked Nov 22 at 14:36
Sam
11
asked Nov 22 at 14:36
Sam
11
asked Nov 22 at 14:36
Sam
11
asked Nov 22 at 14:36
Sam
11
asked Nov 22 at 14:36
Sam
11
11
Well, that depends on how "slow" it really is. Did you check the SQL that's generated (Hibernate will log the SQL query as DEBUG underorg.hibernate.SQL)? If not then you can take that query and directly execute it against your database (you'd need to add the parameters though) and add theexplaincommand to let the database tell you how that query is executed. This should provide some hints on where you could improve it, e.g. by adding some indices.
– Thomas
Nov 22 at 14:50
Thank you for your answer @Thomas , this query can take from 20sec. to 40sec. for almost 600 entities. I was adviced to use joins instead of subqueries. I think that going through so many parametersp.bag.destination.loop.line.changeover.barcode = :barcodeis slowing this down. Is there any way to get around this?
– Sam
Nov 22 at 15:09
Well,p.bag.destination.loop.line.changeover.barcodewould probably result in at least 5 joins but those seem to be to-one relations or otherwise Hibernate would complain. It's hard to tell what exactly is slowing down the query without knowing much more hence my suggestion to useexplainon the resulting SQL query.
– Thomas
Nov 22 at 15:28
1
A few suggestions though: 1) check how many results you'd get without thedistinct- we had a case where a couple of joins resulted in 1 million rows being returned for one (!) distinct entity. 2) check whether there are any sequential scans that could be sped up with an index 3) instead of thenot in <subquery>trynot exists <subquery>.
– Thomas
Nov 22 at 15:31
add a comment |
Well, that depends on how "slow" it really is. Did you check the SQL that's generated (Hibernate will log the SQL query as DEBUG underorg.hibernate.SQL)? If not then you can take that query and directly execute it against your database (you'd need to add the parameters though) and add theexplaincommand to let the database tell you how that query is executed. This should provide some hints on where you could improve it, e.g. by adding some indices.
– Thomas
Nov 22 at 14:50
Thank you for your answer @Thomas , this query can take from 20sec. to 40sec. for almost 600 entities. I was adviced to use joins instead of subqueries. I think that going through so many parametersp.bag.destination.loop.line.changeover.barcode = :barcodeis slowing this down. Is there any way to get around this?
– Sam
Nov 22 at 15:09
Well,p.bag.destination.loop.line.changeover.barcodewould probably result in at least 5 joins but those seem to be to-one relations or otherwise Hibernate would complain. It's hard to tell what exactly is slowing down the query without knowing much more hence my suggestion to useexplainon the resulting SQL query.
– Thomas
Nov 22 at 15:28
1
A few suggestions though: 1) check how many results you'd get without thedistinct- we had a case where a couple of joins resulted in 1 million rows being returned for one (!) distinct entity. 2) check whether there are any sequential scans that could be sped up with an index 3) instead of thenot in <subquery>trynot exists <subquery>.
– Thomas
Nov 22 at 15:31
Well, that depends on how "slow" it really is. Did you check the SQL that's generated (Hibernate will log the SQL query as DEBUG under
org.hibernate.SQL)? If not then you can take that query and directly execute it against your database (you'd need to add the parameters though) and add the explain command to let the database tell you how that query is executed. This should provide some hints on where you could improve it, e.g. by adding some indices.– Thomas
Nov 22 at 14:50
Well, that depends on how "slow" it really is. Did you check the SQL that's generated (Hibernate will log the SQL query as DEBUG under
org.hibernate.SQL)? If not then you can take that query and directly execute it against your database (you'd need to add the parameters though) and add the explain command to let the database tell you how that query is executed. This should provide some hints on where you could improve it, e.g. by adding some indices.– Thomas
Nov 22 at 14:50
Thank you for your answer @Thomas , this query can take from 20sec. to 40sec. for almost 600 entities. I was adviced to use joins instead of subqueries. I think that going through so many parameters
p.bag.destination.loop.line.changeover.barcode = :barcode is slowing this down. Is there any way to get around this?– Sam
Nov 22 at 15:09
Thank you for your answer @Thomas , this query can take from 20sec. to 40sec. for almost 600 entities. I was adviced to use joins instead of subqueries. I think that going through so many parameters
p.bag.destination.loop.line.changeover.barcode = :barcode is slowing this down. Is there any way to get around this?– Sam
Nov 22 at 15:09
Well,
p.bag.destination.loop.line.changeover.barcode would probably result in at least 5 joins but those seem to be to-one relations or otherwise Hibernate would complain. It's hard to tell what exactly is slowing down the query without knowing much more hence my suggestion to use explain on the resulting SQL query.– Thomas
Nov 22 at 15:28
Well,
p.bag.destination.loop.line.changeover.barcode would probably result in at least 5 joins but those seem to be to-one relations or otherwise Hibernate would complain. It's hard to tell what exactly is slowing down the query without knowing much more hence my suggestion to use explain on the resulting SQL query.– Thomas
Nov 22 at 15:28
1
1
A few suggestions though: 1) check how many results you'd get without the
distinct - we had a case where a couple of joins resulted in 1 million rows being returned for one (!) distinct entity. 2) check whether there are any sequential scans that could be sped up with an index 3) instead of the not in <subquery> try not exists <subquery>.– Thomas
Nov 22 at 15:31
A few suggestions though: 1) check how many results you'd get without the
distinct - we had a case where a couple of joins resulted in 1 million rows being returned for one (!) distinct entity. 2) check whether there are any sequential scans that could be sped up with an index 3) instead of the not in <subquery> try not exists <subquery>.– Thomas
Nov 22 at 15:31
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
You can monitor with some tools and you will see how queries behave, normally adding some indices where is needed is enough, if you databases alredy have indices you can do some kind of maintenance to tables you use.
Here is some information
enter link description here
answered Nov 22 at 16:59
Jose
444
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433233%2fhow-to-make-hibernate-query-faster%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
You can monitor with some tools and you will see how queries behave, normally adding some indices where is needed is enough, if you databases alredy have indices you can do some kind of maintenance to tables you use.
Here is some information
enter link description here
answered Nov 22 at 16:59
Jose
444
add a comment |
up vote
0
down vote
You can monitor with some tools and you will see how queries behave, normally adding some indices where is needed is enough, if you databases alredy have indices you can do some kind of maintenance to tables you use.
Here is some information
enter link description here
answered Nov 22 at 16:59
Jose
444
add a comment |
up vote
0
down vote
up vote
0
down vote
You can monitor with some tools and you will see how queries behave, normally adding some indices where is needed is enough, if you databases alredy have indices you can do some kind of maintenance to tables you use.
Here is some information
enter link description here
answered Nov 22 at 16:59
Jose
444
You can monitor with some tools and you will see how queries behave, normally adding some indices where is needed is enough, if you databases alredy have indices you can do some kind of maintenance to tables you use.
Here is some information
enter link description here
answered Nov 22 at 16:59
Jose
444
answered Nov 22 at 16:59
Jose
444
answered Nov 22 at 16:59
Jose
444
answered Nov 22 at 16:59
Jose
444
444
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433233%2fhow-to-make-hibernate-query-faster%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Well, that depends on how "slow" it really is. Did you check the SQL that's generated (Hibernate will log the SQL query as DEBUG under
org.hibernate.SQL)? If not then you can take that query and directly execute it against your database (you'd need to add the parameters though) and add theexplaincommand to let the database tell you how that query is executed. This should provide some hints on where you could improve it, e.g. by adding some indices.– Thomas
Nov 22 at 14:50
Thank you for your answer @Thomas , this query can take from 20sec. to 40sec. for almost 600 entities. I was adviced to use joins instead of subqueries. I think that going through so many parameters
p.bag.destination.loop.line.changeover.barcode = :barcodeis slowing this down. Is there any way to get around this?– Sam
Nov 22 at 15:09
Well,
p.bag.destination.loop.line.changeover.barcodewould probably result in at least 5 joins but those seem to be to-one relations or otherwise Hibernate would complain. It's hard to tell what exactly is slowing down the query without knowing much more hence my suggestion to useexplainon the resulting SQL query.– Thomas
Nov 22 at 15:28
1
A few suggestions though: 1) check how many results you'd get without the
distinct- we had a case where a couple of joins resulted in 1 million rows being returned for one (!) distinct entity. 2) check whether there are any sequential scans that could be sped up with an index 3) instead of thenot in <subquery>trynot exists <subquery>.– Thomas
Nov 22 at 15:31