SQL Server bad performance with a lot of ORs and repeating criteria using UPPER()

user_588240 0
Viewed 400

A software generates a lot of these, non optimal, queries:

SELECT
    <List of Columns>
FROM <Table>
WHERE(
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v1')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v2')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v4')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v6')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v8')) OR
      <...>
     )

Execution plan: https://www.brentozar.com/pastetheplan/?id=rJGtaBzSU

Executing this query results in an Index Seek which takes about 1s to execute. Refactoring the query to the following statement results in an execution time of 3ms:

SELECT
    <List of Columns>
FROM <Table>
WHERE([COL1] = UPPER('CONST_VALUE') AND (
    [COL2] = UPPER('v1') OR
    [COL2] = UPPER('v2') OR
    [COL2] = UPPER('v4') OR
    [COL2] = UPPER('v6') OR
    [COL2] = UPPER('v8') OR
    <...>
    ))

The indexes look optimal afaik, an index on COL1 and COL2 including all selected other columns. Since we can't change the software for now, is there a way to speed up the execution time? Adding a different kind of index. I was also thinking about something like query rewrite or the like, but couldn't find such a thing in SQL Server.

query-performance tsql sql-server sql
2 Answers

If you are able to make changes to the query then remove the UPPER - This can be straightforwardly removed if you are on a case insensitive collation (by far the most common case) - otherwise you will need to add logic to ensure the values are uppercased before being added to the query. UPPER is not constant folded and can give worse plans than simple string literals as shown in the various examples below.

Example Data

CREATE TABLE [Table]
(
[COL1] VARCHAR(20),
[COL2] VARCHAR(10),
PRIMARY KEY ([COL1],[COL2])
)

INSERT INTO [Table]
SELECT TOP 100 'CONST_VALUE',  CONCAT('v', ROW_NUMBER() OVER (ORDER BY @@SPID))
FROM sys.all_columns

Query 1

SELECT *
FROM [Table]
WHERE(
      ([COL1] = 'CONST_VALUE' AND [COL2] = 'V1') OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = 'V1') OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = 'V4') 
     )

The execution plan for this has an index seek operator. Looking at the properties of the plan shows the seek actually contains two different multi column seek predicates (not three seeks. it would be an error to perform the 'V1' seek twice and return those rows twice even though it appears in the WHERE clause twice)

enter image description here

Query 2

SELECT *
FROM [Table] 
WHERE(
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('v1')) OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('V1')) OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('v2')) 
     )

This execution plan looks promising but on closer inspection the seek is only on the single column COL1 - as all rows in the table have the value 'CONST_VALUE' in this case the seek achieves nothing and all the work is done with a residual predicate.

enter image description here

Query 3

SELECT *
FROM [Table] WITH (FORCESEEK)
WHERE(
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('v1')) OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('V1')) OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('v2')) 
     )

This is the same as previous but with a FORCESEEK hint added. The results of UPPER are not constant folded at compile time for some reason so it adds extra operators to the plan to evaluate the UPPER and then collapse down the identical results to perform the two needed multi column index seeks.

enter image description here

Query 4

SELECT *
FROM [Table] 
WHERE(
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v1')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('V1')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v2')) 
     )

Now SQL Server gives up and just gives a scan

enter image description here

Query 5

SELECT *
FROM [Table] 
WHERE [COL1] = UPPER('CONST_VALUE') AND  
(
      [COL2] = UPPER('v1') OR
      [COL2] = UPPER('V1') OR
      [COL2] = UPPER('v2') 
)

This rewrite gives the same execution plan as Query 2 - with a seek on Col1 and residual predicate on Col2, this is not useful with my example data but would be with more realistic cases.

Query 6

SELECT *
FROM sys.all_objects
where 'v1' <> 'v1'

SQL Server detects the contradiction at compile time and gives a very simple plan

enter image description here

Query 7

SELECT *
FROM sys.all_objects
where UPPER('v1') <> UPPER('v1')

Despite the fact that the expressions are deterministic and have exactly the same input values no contradiction detection occurs

enter image description here

user_732260

The problem is that there is not a lot the query optimizer can do in this case. You fall into the trap similar to IN with many arguments -> fallback is table scan.

Put up a table variable with the comparison values, 2 fields and primary key on them, then you can use that table variable in a join and the index on the primary key provides statistics for the query optimizer to work with.

user_2854650
Related