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:
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:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With