SQL VARCHAR vs NVARCHAR in CAST performance

Question

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

similar question was answered http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar — Mattgb, Oct 03 '14 at 10:51
Yes, I know NVARCHAR is Unicode. But it should be simple table permutation. In fact it looks like chars are fetched from the Unicode table using rand() and checking if matched or not — Alexander Zhak, Oct 03 '14 at 10:53
Have you looked at the query execution plan, could it be that it is doing an index scan rather than a seek in these instances. Does an explicit conversion affect your results? — Mattgb, Oct 03 '14 at 11:04
`nvarchar` has higher datatype precedence than `varchar`. Likely an implicit cast on `tblOtherTable.SomeString` prevents a seek. — Martin Smith, Oct 03 '14 at 11:27
@Mattgb I updated the answer with execution plan. Unfortunately I don't know what I want to see on the plan. It differs in 'Index Scan' vs 'Index Seek'. Is this the problem? — Alexander Zhak, Oct 03 '14 at 11:30
Apparently tblOtherTable.SomeString is of type varchar and you cannot seek varchar data with nvarchar value - conversion between varchar and nvarchar is not reversible. — Arvo, Oct 03 '14 at 11:37
@MartinSmith you mean, that using `nvarchar` makes the query invalid? is it possible to make it compliant with `nvarchar` cast? — Alexander Zhak, Oct 03 '14 at 11:37
@Arvo this makes sense. Thanks. tblOtherTable.SomeString is of type 'char' — Alexander Zhak, Oct 03 '14 at 11:38

score 7 · Accepted Answer · answered Oct 03 '14 at 11:39

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.

This explains much. Thanks! – Alexander Zhak Oct 03 '14 at 11:50 — Alexander Zhak, Oct 03 '14 at 11:50

SQL VARCHAR vs NVARCHAR in CAST performance

1 Answers1