Index not used when UNICODE = true?   

Hello,

we've an Java application which select some records from SQL Server 2005. If 
connection parameter unicode is active, every select executes an full table 
scan an the app is very slow (> 1hour). After changing unicode=false, the 
index is used and executing is processed in seconds.

Has someone an idea for this problem?

--
Markus M.
Softwareentwickler/Software Developer

date: Tue, 3 Jun 2008 01:44:00 -0700   author:   MarkusM.

Re: Index not used when UNICODE = true?   

Markus,

How is the table defined?  If the table column is unicode, then the index on 
it is unicode and all should be fine when searching for unicode data. 
However, if the column and index is not unicode, then searching with 
unicode=false should faster.

If you get the table definition, you should see something like one of the 
following:

CREATE TABLE dbo.MyTable
(KeyColumn           INT,
 NonUnicodeData  VARCHAR(1000),
 UnicodeData         NVARCHAR(1000)
);

Your query should match the datatype of the column (and its index) that you 
are searching.

FWIW,
RLF

"MarkusM."  wrote in message 
news:440941DF-A00A-49D9-A42A-2AE32F58B89B@microsoft.com...
> Hello,
>
> we've an Java application which select some records from SQL Server 2005. 
> If
> connection parameter unicode is active, every select executes an full 
> table
> scan an the app is very slow (> 1hour). After changing unicode=false, the
> index is used and executing is processed in seconds.
>
> Has someone an idea for this problem?
>
> --
> Markus M.
> Softwareentwickler/Software Developer

date: Tue, 3 Jun 2008 10:48:25 -0400   author:   Russell Fields

Re: Index not used when UNICODE = true?   

On Jun 3, 1:44 am, MarkusM.  wrote:
> Hello,
>
> we've an Java application which select some records from SQL Server 2005. If
> connection parameter unicode is active, every select executes an full table
> scan an the app is very slow (> 1hour). After changing unicode=false, the
> index is used and executing is processed in seconds.
>
> Has someone an idea for this problem?
>
> --
> Markus M.
> Softwareentwickler/Software Developer

Hi. It's a long-known issue. By default, the driver will send java
character data
as java characters (16-bit). If your table is defined as varchar (8-
bit), the DBMS
can't/won't use any index, because it can't be sure it's conversion of
the 16-bit
data to 8-bit won't corrupt it. If you tell the driver to send char
data as 8-bit,
the DBMS can use varchar indexes. The other solution is to convert the
table
to NVARCHAR.

Joe Weinstein at Oracle

date: Tue, 3 Jun 2008 08:05:06 -0700 (PDT)   author:   unknown

Re: Index not used when UNICODE = true?   

I checked our table and index and it's all non unicode. 
So I think your comments explain the problem.
-- 
--
Markus M.
Softwareentwickler/Software Developer


"joeNOSPAM@bea.com" wrote:

> On Jun 3, 1:44 am, MarkusM.  wrote:
> > Hello,
> >
> > we've an Java application which select some records from SQL Server 2005. If
> > connection parameter unicode is active, every select executes an full table
> > scan an the app is very slow (> 1hour). After changing unicode=false, the
> > index is used and executing is processed in seconds.
> >
> > Has someone an idea for this problem?
> >
> > --
> > Markus M.
> > Softwareentwickler/Software Developer
> 
> Hi. It's a long-known issue. By default, the driver will send java
> character data
> as java characters (16-bit). If your table is defined as varchar (8-
> bit), the DBMS
> can't/won't use any index, because it can't be sure it's conversion of
> the 16-bit
> data to 8-bit won't corrupt it. If you tell the driver to send char
> data as 8-bit,
> the DBMS can use varchar indexes. The other solution is to convert the
> table
> to NVARCHAR.
> 
> Joe Weinstein at Oracle
>

date: Wed, 4 Jun 2008 02:31:02 -0700   author:   MarkusM.