We recently upgraded one of our application to Spring Boot v2.7.12 and used MS SQL Server driver for query execution.
We have been using JTDS driver all this while which is out of support for long time and thus have decided to move to MS SQL driver. MS SQL Server is our database.
Based on some Load testing, it was observed that queries running in our legacy app with jTDS driver complete in few milliseconds, but the exact same queries with new MS SQL Server driver is taking time ranging from 5 sec to 20 secs.
Working with DBAs it seems the execution plan is also different when comparing legacy vs Spring Boot app.
One of the difference is implicit data length, for one of the input parameters of type VARCHAR(20) in DB, query plan on jTDS/legacy shows parameter being passed as VARCHAR(8000), where as query plan for the one with Spring Boot/MS SQL Server driver, the parameter is passed as VARCHAR(4000).
Is there a way in Java/Spring JDBCTemplate that we can pass VARCHAR type input indicating a specific size as VARCHAR(20) to match it what it is in DB?
Has any one encountered same issue while working with different DB drivers and what could be the potential solution.
Expectation is that query with new MS SQL Server driver should be executing in same time as the one with jTDS.