Spring data JPA with SQL Server - lower() function causes full table scan
Multi tool use
In a simple SpringBoot (2.0.5) + JPA + SQL Server microservice with persistence via a JpaRepository implementation (no additional methods), we are seeing the transact-sql lower() function being injected into the generated SQL wrapping the queried column(s), which in turn causes a full table scan and therefore impacts performance on what would otherwise be a simple, fast index-supported query.
Clearly this is to make the queries case-insensitive, however as SQL Server is case-insensitive by default there is no need for this function to be injected. I can see from the Spring Data JPA docs that suffixing repository methods with IgnoreCase should make queries case-insensitive for DBs that are case-sensitive by default, however I can't see how to configure it to respect the incoming case..
JPA config:
jpa:
show-sql: true
hibernate:
naming:
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
dialect: org.hibernate.dialect.SQLServer2012Dialect
ddl-auto: validate
Repository interface:
@Repository
public interface MyRepo extends JpaRepository<Thing, Long>, QuerydslPredicateExecutor<Thing>
I have tried removing the SQLServer2012Dialect from the config - no difference. I am unsure if the additional use of QueryDSL might be having an impact - I shall try temporarily removing that (although it is required).
java
sql-server hibernate spring-data-jpa querydsl asked Nov 13 '18 at 12:11
Tom BuntingTom Bunting
1,069717
add a comment |
In a simple SpringBoot (2.0.5) + JPA + SQL Server microservice with persistence via a JpaRepository implementation (no additional methods), we are seeing the transact-sql lower() function being injected into the generated SQL wrapping the queried column(s), which in turn causes a full table scan and therefore impacts performance on what would otherwise be a simple, fast index-supported query.
Clearly this is to make the queries case-insensitive, however as SQL Server is case-insensitive by default there is no need for this function to be injected. I can see from the Spring Data JPA docs that suffixing repository methods with IgnoreCase should make queries case-insensitive for DBs that are case-sensitive by default, however I can't see how to configure it to respect the incoming case..
JPA config:
jpa:
show-sql: true
hibernate:
naming:
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
dialect: org.hibernate.dialect.SQLServer2012Dialect
ddl-auto: validate
Repository interface:
@Repository
public interface MyRepo extends JpaRepository<Thing, Long>, QuerydslPredicateExecutor<Thing>
I have tried removing the SQLServer2012Dialect from the config - no difference. I am unsure if the additional use of QueryDSL might be having an impact - I shall try temporarily removing that (although it is required).
java
sql-server hibernate spring-data-jpa querydsl asked Nov 13 '18 at 12:11
Tom BuntingTom Bunting
1,069717
Which method are you calling on MyRepo? And I would also say that Querydsl is the problem. I've never seen that hibernate is adding any lower or upper function without telling so
– Simon Martinelli
Nov 13 '18 at 12:34
I think you may be right, I'm going to try removing it this afternoon. This is the method I'm calling (which is indeed fromQuerydslPredicateExecutor:Page<T> findAll(Predicate predicate, Pageable pageable);
– Tom Bunting
Nov 13 '18 at 13:18
and how does the predict look like?
– Simon Martinelli
Nov 13 '18 at 13:23
that was it - see answer ;-)
– Tom Bunting
Nov 13 '18 at 13:29
add a comment |
In a simple SpringBoot (2.0.5) + JPA + SQL Server microservice with persistence via a JpaRepository implementation (no additional methods), we are seeing the transact-sql lower() function being injected into the generated SQL wrapping the queried column(s), which in turn causes a full table scan and therefore impacts performance on what would otherwise be a simple, fast index-supported query.
Clearly this is to make the queries case-insensitive, however as SQL Server is case-insensitive by default there is no need for this function to be injected. I can see from the Spring Data JPA docs that suffixing repository methods with IgnoreCase should make queries case-insensitive for DBs that are case-sensitive by default, however I can't see how to configure it to respect the incoming case..
JPA config:
jpa:
show-sql: true
hibernate:
naming:
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
dialect: org.hibernate.dialect.SQLServer2012Dialect
ddl-auto: validate
Repository interface:
@Repository
public interface MyRepo extends JpaRepository<Thing, Long>, QuerydslPredicateExecutor<Thing>
I have tried removing the SQLServer2012Dialect from the config - no difference. I am unsure if the additional use of QueryDSL might be having an impact - I shall try temporarily removing that (although it is required).
java
sql-server hibernate spring-data-jpa querydsl asked Nov 13 '18 at 12:11
Tom BuntingTom Bunting
1,069717
In a simple SpringBoot (2.0.5) + JPA + SQL Server microservice with persistence via a JpaRepository implementation (no additional methods), we are seeing the transact-sql lower() function being injected into the generated SQL wrapping the queried column(s), which in turn causes a full table scan and therefore impacts performance on what would otherwise be a simple, fast index-supported query.
Clearly this is to make the queries case-insensitive, however as SQL Server is case-insensitive by default there is no need for this function to be injected. I can see from the Spring Data JPA docs that suffixing repository methods with IgnoreCase should make queries case-insensitive for DBs that are case-sensitive by default, however I can't see how to configure it to respect the incoming case..
JPA config:
jpa:
show-sql: true
hibernate:
naming:
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
dialect: org.hibernate.dialect.SQLServer2012Dialect
ddl-auto: validate
Repository interface:
@Repository
public interface MyRepo extends JpaRepository<Thing, Long>, QuerydslPredicateExecutor<Thing>
I have tried removing the SQLServer2012Dialect from the config - no difference. I am unsure if the additional use of QueryDSL might be having an impact - I shall try temporarily removing that (although it is required).
java
sql-server hibernate spring-data-jpa querydsl java
sql-server hibernate spring-data-jpa querydsl asked Nov 13 '18 at 12:11
Tom BuntingTom Bunting
1,069717
asked Nov 13 '18 at 12:11
Tom BuntingTom Bunting
1,069717
edited Nov 13 '18 at 13:31
Tom Bunting
asked Nov 13 '18 at 12:11
Tom BuntingTom Bunting
1,069717
asked Nov 13 '18 at 12:11
Tom BuntingTom Bunting
1,069717
asked Nov 13 '18 at 12:11
Tom BuntingTom Bunting
1,069717
1,069717
Which method are you calling on MyRepo? And I would also say that Querydsl is the problem. I've never seen that hibernate is adding any lower or upper function without telling so
– Simon Martinelli
Nov 13 '18 at 12:34
I think you may be right, I'm going to try removing it this afternoon. This is the method I'm calling (which is indeed fromQuerydslPredicateExecutor:Page<T> findAll(Predicate predicate, Pageable pageable);
– Tom Bunting
Nov 13 '18 at 13:18
and how does the predict look like?
– Simon Martinelli
Nov 13 '18 at 13:23
that was it - see answer ;-)
– Tom Bunting
Nov 13 '18 at 13:29
add a comment |
Which method are you calling on MyRepo? And I would also say that Querydsl is the problem. I've never seen that hibernate is adding any lower or upper function without telling so
– Simon Martinelli
Nov 13 '18 at 12:34
I think you may be right, I'm going to try removing it this afternoon. This is the method I'm calling (which is indeed fromQuerydslPredicateExecutor:Page<T> findAll(Predicate predicate, Pageable pageable);
– Tom Bunting
Nov 13 '18 at 13:18
and how does the predict look like?
– Simon Martinelli
Nov 13 '18 at 13:23
that was it - see answer ;-)
– Tom Bunting
Nov 13 '18 at 13:29
Which method are you calling on MyRepo? And I would also say that Querydsl is the problem. I've never seen that hibernate is adding any lower or upper function without telling so
– Simon Martinelli
Nov 13 '18 at 12:34
Which method are you calling on MyRepo? And I would also say that Querydsl is the problem. I've never seen that hibernate is adding any lower or upper function without telling so
– Simon Martinelli
Nov 13 '18 at 12:34
I think you may be right, I'm going to try removing it this afternoon. This is the method I'm calling (which is indeed from
QuerydslPredicateExecutor: Page<T> findAll(Predicate predicate, Pageable pageable);– Tom Bunting
Nov 13 '18 at 13:18
I think you may be right, I'm going to try removing it this afternoon. This is the method I'm calling (which is indeed from
QuerydslPredicateExecutor: Page<T> findAll(Predicate predicate, Pageable pageable);– Tom Bunting
Nov 13 '18 at 13:18
and how does the predict look like?
– Simon Martinelli
Nov 13 '18 at 13:23
and how does the predict look like?
– Simon Martinelli
Nov 13 '18 at 13:23
that was it - see answer ;-)
– Tom Bunting
Nov 13 '18 at 13:29
that was it - see answer ;-)
– Tom Bunting
Nov 13 '18 at 13:29
add a comment |
1 Answer
1
active
oldest
votes
On closer inspection of the QueryDsl predicate, this was straightforward (and self-inflicted) - changed the use of equalsIgnoreCase() in the predicate to eq() and that has resolved the issue. So no issue in config or libraries, but one to be aware of.
answered Nov 13 '18 at 13:28
Tom BuntingTom Bunting
1,069717
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',
autoActivateHeartbeat: false,
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
);
);
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%2f53280766%2fspring-data-jpa-with-sql-server-lower-function-causes-full-table-scan%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
On closer inspection of the QueryDsl predicate, this was straightforward (and self-inflicted) - changed the use of equalsIgnoreCase() in the predicate to eq() and that has resolved the issue. So no issue in config or libraries, but one to be aware of.
answered Nov 13 '18 at 13:28
Tom BuntingTom Bunting
1,069717
add a comment |
On closer inspection of the QueryDsl predicate, this was straightforward (and self-inflicted) - changed the use of equalsIgnoreCase() in the predicate to eq() and that has resolved the issue. So no issue in config or libraries, but one to be aware of.
answered Nov 13 '18 at 13:28
Tom BuntingTom Bunting
1,069717
add a comment |
On closer inspection of the QueryDsl predicate, this was straightforward (and self-inflicted) - changed the use of equalsIgnoreCase() in the predicate to eq() and that has resolved the issue. So no issue in config or libraries, but one to be aware of.
answered Nov 13 '18 at 13:28
Tom BuntingTom Bunting
1,069717
On closer inspection of the QueryDsl predicate, this was straightforward (and self-inflicted) - changed the use of equalsIgnoreCase() in the predicate to eq() and that has resolved the issue. So no issue in config or libraries, but one to be aware of.
answered Nov 13 '18 at 13:28
Tom BuntingTom Bunting
1,069717
answered Nov 13 '18 at 13:28
Tom BuntingTom Bunting
1,069717
answered Nov 13 '18 at 13:28
Tom BuntingTom Bunting
1,069717
answered Nov 13 '18 at 13:28
Tom BuntingTom Bunting
1,069717
1,069717
add a comment |
add a comment |
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.
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%2f53280766%2fspring-data-jpa-with-sql-server-lower-function-causes-full-table-scan%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
8QHx75RgR,fDKX
Which method are you calling on MyRepo? And I would also say that Querydsl is the problem. I've never seen that hibernate is adding any lower or upper function without telling so
– Simon Martinelli
Nov 13 '18 at 12:34
I think you may be right, I'm going to try removing it this afternoon. This is the method I'm calling (which is indeed from
QuerydslPredicateExecutor:Page<T> findAll(Predicate predicate, Pageable pageable);– Tom Bunting
Nov 13 '18 at 13:18
and how does the predict look like?
– Simon Martinelli
Nov 13 '18 at 13:23
that was it - see answer ;-)
– Tom Bunting
Nov 13 '18 at 13:29