September 23, 2011 at 12:01 pm
I have a 3rd party java app that uses JDBC to pass API cursor calls to my DB. What I'm noticing is that the parameters are being passed as unicode. However, I have no unicode columns in the DB. Thus, unfortunately the query optimizer will perform a table scan instead of using the appropriate index for the columns being searched.
Is there a global config setting that will correct this or can this only be constructed in the java code itself? I have requested the vendor to change their calls but so far no luck. I don't want to start changing columns to nvarchar to "fix" the issue.
Thanks in advance for any advice!
September 23, 2011 at 1:23 pm
I didn't realize it would do that.
I tested on a multi-million row table, and I get a scan of the right index, instead of a seek, but it is a very expensive scan.
The other way around, a varchar parameter on an nvarchar column (indexed), produces a seek, instead of a scan. But an nvarchar parameter on a varchar column (indexed) gets a scan instead of a seek. Interesting.
Check out http://technet.microsoft.com/en-us/library/ms378857(SQL.90).aspx for data about the sendStringParametersAsUnicode parameter in the JDBC connector.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 23, 2011 at 2:28 pm
Thank you!! That's what I was looking for. This is really an inconvenience that SQL Server doesn't handle this, it is really killing performance in our system.
September 26, 2011 at 6:20 am
Won't happen if you use stored procedures in the database. It's because of inline SQL. Not much SQL Server can do about that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 7, 2014 at 12:58 pm
This is an old problem (and solution). Just in case someone still encounters it...
In this case, set sendStringParametersAsUnicode to false for your driver config.
Then when java calls SQLServer it will send ascii strings instead of unicode. If your database is ascii/extended ascii it will be fine. Otherwise SQLServer sees it's a Unicode string from Java, and ascii in the database --- so it doesn't use its indexes.
If it's a numeric column, this won't be a problem of course. And if you use stored procs, not a problem. (The reason why stored procs work I think is because your stored procedure is defining the input characters ascii characters so the call to the stored proc translates the Java unicode into ascii -- and then the indexes are happily used by SQLServer.)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply