I am a DBA and have been tasked with helping an internal app run faster. So, I started off by checking some of the queries that have been pushed through. Right off the bat I see ton's of queries with implicit conversions happening. I checked the schema definitions and the columns are varchar and the current plan shows the variables coming in as nvarchar. Voila...implicit conversions all over the place. So I read through the jTDS documentation and found a setting to try and help eliminate this problem. So we made the adjustment to the connection string and included this: sendStringParametersAsUnicode="false". Had them restart the app server for good measure. Clear the proc cache and start the tests. And the same exact queries are showing up with the implicit conversions again. We are currently using jTDS 1.2.4. Is this option ignored in this older version of the driver? Are there other options I need to suggest as a way to get rid of these conversions? The query when copy and pasted in SSMS produces an execution plan with index seeks like it should, but when it comes from the app, the implicit conversion makes the exec plan turn into garbage and perform index scans.
Thanks in advance.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am a DBA and have been tasked with helping an internal app run faster. So, I started off by checking some of the queries that have been pushed through. Right off the bat I see ton's of queries with implicit conversions happening. I checked the schema definitions and the columns are varchar and the current plan shows the variables coming in as nvarchar. Voila...implicit conversions all over the place. So I read through the jTDS documentation and found a setting to try and help eliminate this problem. So we made the adjustment to the connection string and included this: sendStringParametersAsUnicode="false". Had them restart the app server for good measure. Clear the proc cache and start the tests. And the same exact queries are showing up with the implicit conversions again. We are currently using jTDS 1.2.4. Is this option ignored in this older version of the driver? Are there other options I need to suggest as a way to get rid of these conversions? The query when copy and pasted in SSMS produces an execution plan with index seeks like it should, but when it comes from the app, the implicit conversion makes the exec plan turn into garbage and perform index scans.
Thanks in advance.