Migrating from Oracle to Azure SQL caveat — prepared statement set string causes implicit conversion

Problem

While monitoring the azure database for issues after the migration, we noticed very high CPU and memory consumption because of a query that ran continuously and required an index scan. We have an index on the filtering column in the where clause, and the query returned only one record per run. Therefore, we were intrigued as to why the query caused high CPU usage. In fact, when we ran the query directly via SQL Server Management Studio, we got the result almost instantaneously because the database performed an index seek as expected. The slowness only occurred when the query originated from our java application.

SELECT id, column_1 from mySchema.myTable Where stringId = @PO
String selectByStringId = "SELECT id, column_1 cd from mySchema.myTable WHERE stringId = ?";
String id = "abcde"
Connection connection = getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(selectByStringId);
preparedStatement.setString(1, id);
ResultSet resultSet = preparedStatement.executeQuery();

Reason

In the codes, we used setString() method on the prepared statement to set the id, which is a string. When sending the parameter, Microsoft SQL JDBC driver set the type as nvarchar, which is for Unicode. However, the corresponding column's type is varchar. Because nvarchar precedes varchar, the database implicitly converted the value to varchar. The implicit conversion caused the high CPU usage.

Solution

We needed a way to avoid implicit conversion from happening. One way is to migrate the data type of the target column from varchar to nvarchar. However, we did not go with this approach since we have not had the need to support Unicode characters, and using nvarchar could take more space.

References

Performance degradation due to implicit Conversion (microsoft.com)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Tai Bo

Tai Bo

Backend developer in .NET core. I enjoy the outdoor, hanging out with good friends, reading and personal development.