Query is slow with JDBC parameters, fast with concatenated SQL
I have an application that can be run with either mysql or SQL Server. There is a query that runs in a reasonable time on mysql, but is very slow on SQL Server. My application uses hibernate, but this particular query is coded in raw SQL so I know exactly what SQL is being executed. If I run the same query on SQL Server directly in a database client, it runs quickly. It's only slow when run in my application.
I discovered that if I take out the use of JDBC parameters and just concatenate the values into the SQL string, it runs much much faster. However this isn't a good solution; it doesn't guard against SQL injection or handle characters that might need escaping.
I have found recommendations to use sendStringParametersAsUnicode=false on the connection, but this isn't appropriate for my case because the columns are all nvarchar. In any case, I did try this and it didn't make anything faster and made some things slower. I also found info that suggested the jtds driver is faster, but it's not compatible with hibernate so I have to use the Microsoft driver.
My slow-running code looks like this:
String sql = " SELECT DISTINCT FOO.BAR_ID_ "
[big join here across multiple tables]
"WHERE FOO.ID_= :fooId " +
" AND BAZ.NAME_ LIKE :likeParameter ";
Query query = getSession().createSQLQuery(sql)
.setParameter("fooId", fooId)
.setParameter("likeParameter", likeParameter);
return query.list();
It runs quickly if I change it to this:
String sql = " SELECT DISTINCT FOO.BAR_ID_ "
[big join here across multiple tables]
" WHERE FOO.ID_= " + fooId +
" AND BAZ.NAME_ LIKE '" + likeParameter + "'";
Query query = getSession().createSQLQuery(sql);
return query.list();
The LIKE parameter has wildcards on both ends, which I would expect to prevent the use of an index on that column, but that would be true for the concatenation case as well, and it runs quickly. Clearly there is some other issue with SQL Server and JDBC parameters besides the Unicode issue. What can I do to get around this?
sql-server query-performance jdbc
asked 7 mins ago
DanaDana
1011
New contributor
Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I have an application that can be run with either mysql or SQL Server. There is a query that runs in a reasonable time on mysql, but is very slow on SQL Server. My application uses hibernate, but this particular query is coded in raw SQL so I know exactly what SQL is being executed. If I run the same query on SQL Server directly in a database client, it runs quickly. It's only slow when run in my application.
I discovered that if I take out the use of JDBC parameters and just concatenate the values into the SQL string, it runs much much faster. However this isn't a good solution; it doesn't guard against SQL injection or handle characters that might need escaping.
I have found recommendations to use sendStringParametersAsUnicode=false on the connection, but this isn't appropriate for my case because the columns are all nvarchar. In any case, I did try this and it didn't make anything faster and made some things slower. I also found info that suggested the jtds driver is faster, but it's not compatible with hibernate so I have to use the Microsoft driver.
My slow-running code looks like this:
String sql = " SELECT DISTINCT FOO.BAR_ID_ "
[big join here across multiple tables]
"WHERE FOO.ID_= :fooId " +
" AND BAZ.NAME_ LIKE :likeParameter ";
Query query = getSession().createSQLQuery(sql)
.setParameter("fooId", fooId)
.setParameter("likeParameter", likeParameter);
return query.list();
It runs quickly if I change it to this:
String sql = " SELECT DISTINCT FOO.BAR_ID_ "
[big join here across multiple tables]
" WHERE FOO.ID_= " + fooId +
" AND BAZ.NAME_ LIKE '" + likeParameter + "'";
Query query = getSession().createSQLQuery(sql);
return query.list();
The LIKE parameter has wildcards on both ends, which I would expect to prevent the use of an index on that column, but that would be true for the concatenation case as well, and it runs quickly. Clearly there is some other issue with SQL Server and JDBC parameters besides the Unicode issue. What can I do to get around this?
sql-server query-performance jdbc
asked 7 mins ago
DanaDana
1011
New contributor
Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I have an application that can be run with either mysql or SQL Server. There is a query that runs in a reasonable time on mysql, but is very slow on SQL Server. My application uses hibernate, but this particular query is coded in raw SQL so I know exactly what SQL is being executed. If I run the same query on SQL Server directly in a database client, it runs quickly. It's only slow when run in my application.
I discovered that if I take out the use of JDBC parameters and just concatenate the values into the SQL string, it runs much much faster. However this isn't a good solution; it doesn't guard against SQL injection or handle characters that might need escaping.
I have found recommendations to use sendStringParametersAsUnicode=false on the connection, but this isn't appropriate for my case because the columns are all nvarchar. In any case, I did try this and it didn't make anything faster and made some things slower. I also found info that suggested the jtds driver is faster, but it's not compatible with hibernate so I have to use the Microsoft driver.
My slow-running code looks like this:
String sql = " SELECT DISTINCT FOO.BAR_ID_ "
[big join here across multiple tables]
"WHERE FOO.ID_= :fooId " +
" AND BAZ.NAME_ LIKE :likeParameter ";
Query query = getSession().createSQLQuery(sql)
.setParameter("fooId", fooId)
.setParameter("likeParameter", likeParameter);
return query.list();
It runs quickly if I change it to this:
String sql = " SELECT DISTINCT FOO.BAR_ID_ "
[big join here across multiple tables]
" WHERE FOO.ID_= " + fooId +
" AND BAZ.NAME_ LIKE '" + likeParameter + "'";
Query query = getSession().createSQLQuery(sql);
return query.list();
The LIKE parameter has wildcards on both ends, which I would expect to prevent the use of an index on that column, but that would be true for the concatenation case as well, and it runs quickly. Clearly there is some other issue with SQL Server and JDBC parameters besides the Unicode issue. What can I do to get around this?
sql-server query-performance jdbc
asked 7 mins ago
DanaDana
1011
New contributor
Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have an application that can be run with either mysql or SQL Server. There is a query that runs in a reasonable time on mysql, but is very slow on SQL Server. My application uses hibernate, but this particular query is coded in raw SQL so I know exactly what SQL is being executed. If I run the same query on SQL Server directly in a database client, it runs quickly. It's only slow when run in my application.
I discovered that if I take out the use of JDBC parameters and just concatenate the values into the SQL string, it runs much much faster. However this isn't a good solution; it doesn't guard against SQL injection or handle characters that might need escaping.
I have found recommendations to use sendStringParametersAsUnicode=false on the connection, but this isn't appropriate for my case because the columns are all nvarchar. In any case, I did try this and it didn't make anything faster and made some things slower. I also found info that suggested the jtds driver is faster, but it's not compatible with hibernate so I have to use the Microsoft driver.
My slow-running code looks like this:
String sql = " SELECT DISTINCT FOO.BAR_ID_ "
[big join here across multiple tables]
"WHERE FOO.ID_= :fooId " +
" AND BAZ.NAME_ LIKE :likeParameter ";
Query query = getSession().createSQLQuery(sql)
.setParameter("fooId", fooId)
.setParameter("likeParameter", likeParameter);
return query.list();
It runs quickly if I change it to this:
String sql = " SELECT DISTINCT FOO.BAR_ID_ "
[big join here across multiple tables]
" WHERE FOO.ID_= " + fooId +
" AND BAZ.NAME_ LIKE '" + likeParameter + "'";
Query query = getSession().createSQLQuery(sql);
return query.list();
The LIKE parameter has wildcards on both ends, which I would expect to prevent the use of an index on that column, but that would be true for the concatenation case as well, and it runs quickly. Clearly there is some other issue with SQL Server and JDBC parameters besides the Unicode issue. What can I do to get around this?
sql-server query-performance jdbc
sql-server query-performance jdbc
asked 7 mins ago
DanaDana
1011
New contributor
Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 7 mins ago
DanaDana
1011
New contributor
Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 7 mins ago
DanaDana
1011
New contributor
Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 7 mins ago
DanaDana
1011
asked 7 mins ago
DanaDana
1011
1011
New contributor
Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
Dana is a new contributor. Be nice, and check out our Code of Conduct.
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%2fdba.stackexchange.com%2fquestions%2f231109%2fquery-is-slow-with-jdbc-parameters-fast-with-concatenated-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Dana is a new contributor. Be nice, and check out our Code of Conduct.
Dana is a new contributor. Be nice, and check out our Code of Conduct.
Dana is a new contributor. Be nice, and check out our Code of Conduct.
Dana is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f231109%2fquery-is-slow-with-jdbc-parameters-fast-with-concatenated-sql%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
