Where the Problem Started
When an index exists but is not used, intuition usually fails until the execution plan is inspected. With an ORM, a condition that looks harmless in application code can become a different plan once JDBC parameter types and database type conversion are involved. This post follows a SQL Server case where a VARCHAR index was skipped.
I observed that when an ORM uses a string value in a query, it sends the query using the NVARCHAR type. So when an index contains columns declared as VARCHAR, and the query tries to use that index, a type mismatch causes the index not to be used.
For example, consider PK__CUST_INFO__7AA72534 (CARE_ORG_ID char(8), CUST_NO varchar(20)), a simple two-column index.
I also suspected that statistics might have had an error and prevented the index from being used smoothly, so I ran a query that ignores statistics through option(recompile).
Implementation Path
select * from cust_info where CARE_ORG_ID = '15887361' and CUST_NO = '#1030197' option(recompile)
select * from cust_info where CARE_ORG_ID = N'15887361' and CUST_NO = N'#1030197' option(recompile)How I Verified It
This is Query Analysis in DataGrip. The first query used the intended index, but the second query, which uses nvarchar parameters, unexpectedly performed an INDEX_CUST_INFO_CELL_PHONE index seek before using the PK index.
Because the CELL_PHONE index is non-clustered, it does not contain every column. SQL Server appears to fetch candidate rows from that index and then join/look up the remaining row data through the PK.
This is the result of dropping the CELL_PHONE index from a copied CUST_INFO table and running the query with nvarchar params.
The inner join in a single-table query looks odd at first. My inference is that because the parameters were bound as nvarchar, SQL Server had to convert CARE_ORG_ID and CUST_NO values to nvarchar during plan execution, which broke the expected direct index usage.
I then changed both CARE_ORG_ID and CUST_NO to nvarchar and reran the same two queries.
Performance Takeaway
An ORM hides SQL generation, but it does not hide database behavior. Parameter type, implicit conversion, collation, and index column type can make a good index useless. Performance debugging has to read application code and the actual database execution plan together.
When the column types are also nvarchar, the index is used regardless of whether the parameter is sent as nvarchar or varchar.
Why did the previous table column type conversion happen? This can be checked at https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16.
In short, when querying a varchar type column with nvarchar, nvarchar has higher precedence, so type conversion occurs on every row of the entire table. But when querying an nvarchar column with varchar, conversion occurs only on the query parameter before the query runs, so the same query plan occurs.
The fact that even the simplest form of index failed to be used made it clear that all queries executed by the ORM, where there are often joins across as many as 10 or more tables, were not using the intended indexes at all. Three possible solutions come to mind.
- Convert every column type used by indexes to nvarchar.
Because nvarchar supports Unicode, unlike varchar/char, which uses 1 byte per character, it consumes 2 bytes per character. This increases not only the overall database size but also the index size.
Connected to the above, because the data value becomes larger, I/O work takes more time and memory usage also increases.
Changing a column’s type requires dropping the index, changing the type, and recreating the index if that column is used in an index.
We confirmed above that even when using nvarchar columns, a varchar parameter can still use the index, but there may be unknown side effects, especially in programs that use native query.
- Keep string parameters in the ORM as nvarchar by default, but exceptionally send queries as varchar/char for columns that need to use indexes.
This is the most ideal solution, but applying it in a Spring Boot project is strangely painful. I will write a separate document for it.
- Conversely, make all string parameters varchar, and handle some columns declared as nvarchar.