Unicode conversions by default causing poor performance

Question

Post reply

Unicode conversions by default causing poor performance

Master of None-204979

SSC Journeyman

Points: 99
More actions
December 22, 2009 at 1:24 pm

#207669

Something I spent a great deal of time tracking down. Thought I would share my findings:
I recently discovered that an application using JDBC for sql server to connect to the database was generating sp_prepexec calls when generating SQL. All attempts to optimize the queries were unsuccessful. No matter what indices were added, I could not get the optimizer to use them.
Upon further examination of the queries, I noticed that all values of type char and varchar were being converted to nchar and nvarchar. Furthermore, each parameter in the WHERE clause was being converted to Unicode on the fly (prefaced with N, i.e. @parm1=N'MyValue'). After researching, I found documentation that stated the DEFAULT connection property of sendStringParametersAsUnicode is TRUE. This is why everything was being converted.
After having the developers change that setting to FALSE, all the indices were used! Problem solved. Hoep this helps someone.
http://msdn.microsoft.com/en-us/library/ms378988.aspx

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Good to know. Thank you. Have you thought about writing this up as an article for ssc? You should.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Lynn Pettis (12/22/2009)
Good to know. Thank you. Have you thought about writing this up as an article for ssc? You should.

I second the motion.:cool:

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Master of None-204979 SSC Journeyman Points: 99 More actions · Answer 3

CirquedeSQLeil (12/22/2009)
Lynn Pettis (12/22/2009)
Good to know. Thank you. Have you thought about writing this up as an article for ssc? You should.
I second the motion.:cool:

Thanks for the feedback. I'm pretty swamped at the moment but perhaps at a later date. The most important lesson here is that these conversions to unicode meant that the indexes could not be used since they are encoded as non-unicode chars and varchars.