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 (mssql-jdbc-7.0.0.jre8.jar).
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?
UPDATE: here are the query plans:
Fast query with in-line data: https://www.brentozar.com/pastetheplan/?id=SkkH0NiU4
Slow query with parameters: https://www.brentozar.com/pastetheplan/?id=SkBxhQoLE