SQL Server Query Performance: Index Seek in SSMS but Index Scan in Java Application

Question

I'm facing an issue with SQL Server query performance and I'm hoping to get some insights from you all.

Problem Statement:

I have a SQL query that performs well (~6 ms) when executed directly in SQL Server Management Studio (SSMS), showing an "Index Seek" in the execution plan. However, when the same query is executed from my Java application using JDBC, the execution plan shows an "Index Scan" and the query takes significantly longer (~1 Sec) to execute.

What I've Tried:

1. Ensured that statistics are up-to-date on the tables and indexes involved.
2. Compared the execution context settings between SSMS and the Java application.
3. Added SQL into Stored prod and then it works fine, does index seek.
4. Removed plan id, cleared cache.

Question:

1. What could be causing this discrepancy in query performance?
2. Are there any debugging steps that I might have missed?

Plan when this is executed from Java: https://www.brentozar.com/pastetheplan/?id=SkWWezsya

Plan when this is executed from SSMS: https://www.brentozar.com/pastetheplan/?id=HJZPefokp

Any insights or suggestions would be greatly appreciated.

Answer 1

The query plans in your question show the SSMS parameter data type is varchar(8000) whereas the JDBC parameter type is nvarchar(4000). The SSMS plan is able to use an efficient seek operator because the varchar parameter type matches the varchar column type but the implicit conversion of the column to nvarchar in the JDBC plan per data type precedence rules results in a non-sargable expression, requiring a scan.

I'll add that the implicit conversion would not be much of an issue with a Windows collation because SQL Server could coerce a sargable expression with a range seek predicate using GetRangeThroughConvert.

You can pass varchar parameters from JDBC apps by specifying sendStringParametersAsUnicode=false in the connection string. Relevant excerpt from the JDBC doc:

For optimal performance with the CHAR, VARCHAR, and LONGVARCHAR JDBC data types, an application should set the sendStringParametersAsUnicode property to "false" and use the setString, setCharacterStream, and setClob non-national character methods of the SQLServerPreparedStatement and SQLServerCallableStatement classes.

If you also need to pass Unicode parameters, use the setN methods of SQLServerPreparedStatement:

An application should use the setNString, setNCharacterStream, and setNClob national character methods of the SQLServerPreparedStatement and SQLServerCallableStatement classes for the NCHAR, NVARCHAR, and LONGNVARCHAR JDBC data types.

Regarding:

3. Added SQL into Stored prod and then it works fine, does index seek.

The stored procedure parameter was apparently defined as varchar. In this case, the application-provided nvarchar parameter value was implicitly converted to the proc-defined varchar parameter before the procedure was executed. The proc execution plan then used the varchar parameter without any conversion, allowing the index seek.

It's worth mentioning that stored procedures, with proper parameter types, can help mitigate issues with improper parameter types passed by client applications because the procedures are compiled and optimized with the proc parameter definition rather than the client-supplied parameter definition.

Answer 2

Yep, as per comments by @DanGuzman and @DenisRubashkin, you have a nvarchar parameter.

<ParameterList>
              <ColumnReference Column="@P0" ParameterDataType="nvarchar(4000)" ParameterCompiledValue="N'5008K0000046zJGQAY'" />
</ParameterList>

You will have to coerce your client to send as a varchar.

I see this all the time in .NET/Entity Framework/LINQ. It's insidious because it kind of, sort of works in the small dev/test/UAT data sets but performance issues manifest in production workloads & data sets.

Parameters added with AddWithValue or similar will do it. Native unicode == nvarchar not varchar and as ANSI SQL demands a conversion on the table values not the parameters you get scans on the index rather than proper seeks :(

Answer 3

The XML from your Java plan confirms the implicit conversion from VARCHAR to NVARCHAR is affecting the plan:

<PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(18),[CCIP].[salesforce].[Case].[Id],0)=[@P0]" />