0

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

  • My intuition is that the type passed to the server is in some way different when you are using bind variables and it is having to perform the type conversion implicitly to do the comparison for each row – Gaius Mar 2 at 15:34
  • Post the query plans from the slow and fast executions. brentozar.com/pastetheplan – Tony Hinkle Mar 2 at 22:25
  • @TonyHinkle I've added these to the question. – Dana Mar 4 at 20:20
  • Those two plans look basically the same, but those are estimated plans. Can you run both versions capturing the actual plans and the query runtime? brentozar.com/pastetheplan/instructions – David Browne - Microsoft Mar 4 at 21:04
  • @DavidBrowne-Microsoft I've updated the links to point to the actual plans. – Dana Mar 4 at 22:56
1

Not a complete answer, but here's some diagnostics and suggestions from looking at the plans.

You're running SQL 2016, so there's a ton of useful info in the plan.

First is that you're running a GDR patch for SQL 2016 SP1 (13.0.4224.16). SQL 2016 SP2 was released on April 24, 2108, almost a year ago, and after 1 year applying the service pack is required for support. So you can test this on SQL 2016 SP2, or SQL 2017 to see if it behaves differently.

When optimizing a parameterized query, SQL Server has to pick a plan that will be used for all future values of fooId and likeParameter. Apparently it's guessing you will often have a selective LIKE predicate, and so using the Name index will, on average, be cheaper. With the hard-coded values, SQL Server doesn't need to build a plan that works for multiple different LIKE predicates, and so picks a plan that doesn't use a non-clustered index.

To evaluate the predicate NAME_ LIKE @p SQL Server is range scanning a non-clustustered index on NAME_. This works great if you happen to pass a LIKE predicate that starts with a few literal characters, but when it starts with a wildcard, will require a complete scan of the index. Here scanning, and evaluating the LIKE predicate for each of 14M rows.

In the fast plan SQL seeks 21,518 separate components using the clustered index and checks each one's name against the LIKE predicate.

From the plans:

The difference between the plans is a single operator, which accounts for all of the increased cost: a scan of 14,675,300 rows from the index [PDL_COMPONENT].[IDX_COMPONENT_NAME], taking 37sec of CPU time:

  <RunTimeInformation>
    <RunTimeCountersPerThread 
      Thread="0" 
      ActualRows="741" 
      Batches="0" 
      ActualEndOfScans="1" 
      ActualExecutions="1" 
      ActualExecutionMode="Row" 
      ActualElapsedms="37038" 
      ActualCPUms="37028" />
  </RunTimeInformation>

Not parameter sniffing, as the ParameterCompiledValue is the same as ParemeterRuntimeValue:

    <ParameterList>
      <ColumnReference Column="@P1" ParameterDataType="nvarchar(127)" ParameterCompiledValue="N'%geronimo%'" ParameterRuntimeValue="N'%geronimo%'" />
      <ColumnReference Column="@P0" ParameterDataType="int" ParameterCompiledValue="(9)" ParameterRuntimeValue="(9)" />
    </ParameterList>

Using the query with the hard-coded values isn't the worst idea here. Or you could try a RECOMPILE and turn off plan caching without giving up parameterizing your queries.

And I don't know what the indexes and foreign keys in the database are, but using DISTINCT has really bad code smell, as it often indicates that one or more JOIN is not correct, and it affects the cardinality estimation of the query.

  • "Apparently it's guessing you will often have a selective LIKE predicate, and so using the Name index will, on average, be cheaper. " This seems like a really bad call given that a LIKE predicate could have a wildcard at the front, and the table is big… – Dana Mar 5 at 4:49
  • It’s also guessing about the average number of rows that make it this far. If there are a large number of rows to check, scanning the index will be cheaper. – David Browne - Microsoft Mar 5 at 13:04
  • I voted this up because it helped me diagnose, but didn't select it because it didn't solve the problem. – Dana Mar 28 at 21:44
0

In the end I was not able to solve this in the database, but I took the LIKE predicate out of the query altogether, ran the rest of the query and filtered the results in java. For my use case this was much faster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.