4

We have a MS SQL Server 2005 installation that connects to an Oracle database through a linked server connection.

Lots of SELECT statements are being performed through a series of OPENQUERY() commands. The WHERE clause in the majority of these statements are against VARCHAR columns.

I've heard that if the WHERE clause is case sensitive, it can have a big impact on performance.

So my question is, how can I make sure that the non-binary string WHERE clauses are being performed in a case insensitive way for maximum performance?

Peter Bridger
  • 9,123
  • 14
  • 57
  • 89
  • Is the basic objective to improve performance? First confirm you have a performance issue due to this (you probably don't) otherwise you could be introducing unnecessary code which in fact causes a performance issue. I believe the that what you 'heard' is a myth or a misunderstanding. – Nick.Mc Sep 25 '15 at 01:37

5 Answers5

6

It's actually the other way around:

Case sensitive...

WHERE column = :criteria

...will use index on column directly and perform well.

Case insensitivity typically requires something like this...

WHERE UPPER(column) = UPPER(:criteria)

...which does not use index on column and performs poorly (unless you are careful and create a functional index on UPPER(column)).

I'm not sure whether OPENQUERY() changes anything, but from purely Oracle perspective both case-sensitive and insensitive queries can be made performant, with the insensitive ones requiring special care (functional index).

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • 1
    This is the most complete answer because it includes the caveat about poor performance with UPPER or LOWER and the use of a functional index. The other answers omit this and this can cause an unwelcome surprise on a live system with significant data. – Jeff Mergler Mar 16 '21 at 19:08
4

By default SQL server uses a case insensitive collation where Oracle is case sensitive by default. For searches we normally implement the Upper() comparison to ensure the user has a better search experience.

tsells
  • 2,751
  • 1
  • 18
  • 20
2
WHERE LOWER(field_name) = 'field_value'
Blue
  • 22,608
  • 7
  • 62
  • 92
TarasM
  • 27
  • 1
  • 7
  • 4
    Welcome to SO, TarasM! Code-only answers are discouraged here, as they provide no insight into how the problem was solves. Please update your solution with an explanation of how your code solves the problem at hand :) – Joel Oct 31 '18 at 21:50
  • 1
    Additionally, please note why you've added a new answer that looks completely like the existing ones – Nico Haase Nov 01 '18 at 08:32
2

I've heard that if the WHERE clause is case sensitive, it can have a big impact on performance.

From where did you hear that? Sounds like a myth to me... rather it would be other way around, ie if you'd use something like WHERE lower(field) = 'some str' to achieve case-insentive comparision it would be bad on perfomance. Using case-insensitive collation would probably be significantly faster...

Another important point to consider is do your business rules actually allow case-insensitive comparision.

And last but not least, you should start to optimize when you indeed do have a perfomance problem, not because you heard something...

ain
  • 22,394
  • 3
  • 54
  • 74
0

To make WHERE clause case insensitive, you can use LOWER or UPPER for this purpose. 

 select * from Table_Name
 where  lower(Column_Name) = lower('mY Any Value')

OR

select * from Table_Name
where  UPPER(Column_Name) = UPPER('mY Any Value')
Arsman Ahmad
  • 2,000
  • 1
  • 26
  • 34