I just tried a simple exclusion query of the form

select * from T1 where C1 not in (select C2 from T2)

where C1 is a varchar column.

That works very fast if C1 and C2 are both varchar types.

It runs horrible slow when C1 is a nvarchar type and C2 is not.

It runs fast again when C1 is explicetely casted as in

select * from T1 where cast(C1 as varchar) not in (select C2 from T2)

Is this kind of implicit string conversion between varchar and nvarchar an exclusion for a "sargable" predicate?

(I'm not dealing with this particular case, just asking if this is a general rule of thumb: To cast any search predicate when using IN expressions and the like?)

I'm running SA 11.0.1.2331.

asked 27 Nov '09, 16:43

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 27 Nov '09, 16:53


The short answer is yes.

To ensure safety in doing comparison operations, SQL Anywhere must in many cases avoid using an index for value lookups if the domain of the lookup value is not identical to the domain of the indexed column.

There is a whitepaper that explains this in detail for all other data types (since the paper was written for Vrsion 9, it contains nothing about CHAR - NCHAR comparisons.

permanent link

answered 28 Nov '09, 03:08

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

Glenn, thanks for the pointer. The whitepaper makes it all clear, and it seems understandable that CHAR vs. NCHAR falls in the category of "not the same type", too. The learning curve goes on ever and ever:)

(28 Nov '09, 21:19) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×20
×3
×1

question asked: 27 Nov '09, 16:43

question was seen: 811 times

last updated: 28 Nov '09, 03:08