Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL VARCHAR vs NVARCHAR in CAST performance

I have a query which compares data in two tables:

SELECT DISTINCT
    MT.Column1,
    MT.Column2,
    MT.Column5,
    MT.Column7,
    MT.Column9
FROM tblMyTable MT
WHERE
    EntryDate >= @StartDate AND EntryDate <= @EndDate AND
    NOT EXISTS (
        SELECT ID FROM tblOtherTable
        WHERE SomeString LIKE 
                'X' + CAST(MT.Column1 AS VARCHAR(16)) +
                'Y' + CAST(MT.Column3 AS VARCHAR(16)) +
                'Z' + CAST(MT.Column4 AS VARCHAR(16)) + '%'
    )

It works OK. But when I'm trying to use CAST(var AS NVARCHAR), the query executes over 10 minutes and doesn't seem to finish in the nearest future. But when I change to CAST(var AS VARCHAR) as above, the query finishes in 2-3 seconds.

CASTed columns are defined as:

  • Column1 int, not null,
  • Column3 varchar(50), not null
  • Column4 varchar(9),not null

but in fact all contain ONLY numbers, 9-15 digits in length

I wonder what could be the reason for such performance loss?

UPDATE:

Execution plan shows the folowing: enter image description here

like image 812
Alexander Zhak Avatar asked Oct 03 '14 10:10

Alexander Zhak


1 Answers

The nvarchar data type has a higher data type precedence. So with the nvarchar CAST, the indexed column must first be converted to nvarchar and the index cannot be used for the more efficient seek as a result.

The indexed column is already varchar so no column converstion is needed in that case. The index can be used for the more efficient seek data access path in the execution plan.

This behavior is known as a sargable. See http://en.wikipedia.org/wiki/Sargable.

like image 149
Dan Guzman Avatar answered Sep 29 '22 12:09

Dan Guzman