Bind Variable Dramatically Slows Query: ASCII vs Unicode mismatch

[bill]

I was building a table-valued function that included this in the WHERE
clause:
AND tab.SOME_COLUMN = '0148'

I wanted to drive the query with a parameter and changed the WHERE
statement to this:
AND tab.SOME_COLUMN = @SOME_VARIABLE_CD

Simply substituting the variable for the literal caused the query to
run _four times slower_.

I checked my DECLARE statement, and found this:
DECLARE @SOME_VARIABLE_CD nvarchar(4)

The database column tab.SOME_COLUMN is ASCII (varchar) not Unicode
(nvarchar).

I changed my variable declaration to ASCII data type:
DECLARE @SOME_VARIABLE_CD varchar(4)

The query zipped back to its original speed

I suspect the performance hit when comparing varchar to nvarchar
is related to implicit conversions. However, my suspicion does not
seem to be borne out by the query plan.

I have not had a chance to test with a Unicode database.
Does anyone know why the simple DECLARE change
would have a 4x impact on query speed?

Regardless, I am going to make sure that the
ASCII/UNICODE nature of my variables matches my columns.

Thanks,

Bill

[joe.we...@gmail.com]

Hi. It is to do with conversions. The DBMS can't be sure that
converting
will retain the same ordering, and so it won't use indexes on that
column,
so you get a table scan.
Joe Weinstein at Oracle

[Plamen Ratchev]

If you check the execution plan of the query you should see the implicit conversion for the data types:

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol VARCHAR(30));

INSERT INTO Foo VALUES(1, 'a');
INSERT INTO Foo VALUES(2, 'b');
INSERT INTO Foo VALUES(3, 'c');

GO

SET SHOWPLAN_TEXT ON;

GO

DECLARE @search NVARCHAR(30);

SET @search = N'b';

SELECT keycol, datacol
FROM Foo
WHERE datacol = @search;

/*

StmtText
--------------------------------------------------------------------------------------
|--Clustered Index Scan(OBJECT:([Testing].[dbo].[Foo].[PK__Foo__98D78B4403439144]),
WHERE:(CONVERT_IMPLICIT(nvarchar(30),[Testing].[dbo].[Foo].[datacol],0)=[@search]))

*/

GO

SET SHOWPLAN_TEXT OFF;

GO

DROP TABLE Foo;

--
Plamen Ratchev
http://www.SQLStudio.com

[bill]

Thanks to both of your for the replies.

Plamen, thanks for the code. I had expected to see something like
this when I ran the alternate plans prior to posting, but I guess I
just missed it (plan was pretty large). It.

This little test was better because it jumped off the page.

Thanks,

Bill

[bill]

Here's something interesting: The engine appears to use the index,
even if it has to do an implicit conversion.

So it isn't doing a scan, but that conversion seems to be pretty
expensive.

Using Plamens 'Foo' table (see above)

I ran this:
------------------------
DECLARE @search Nvarchar(30);
SET @search = N'1';

SELECT keycol, datacol
FROM Foo

WHERE keycol = @search;

And got this (Seek, not scan, but note the implicit CONVERT):
------------------------
|--Clustered Index Seek(OBJECT:([TICDW].[dbo].[Foo].
[PK__Foo__98D78B442690946A]), SEEK:([TICDW].[dbo].[Foo].[keycol]
=CONVERT_IMPLICIT(int,[@search],0)) ORDERED FORWARD)

Then I chanced the variable declaration to match the INT data type o
fthe key:
----------------------------
DECLARE @search int;
SET @search = 1;

SELECT keycol, datacol
FROM Foo

WHERE keycol = @search;

Now the engine does not do the implicit CONVERT
----------------------
|--Clustered Index Seek(OBJECT:([TICDW].[dbo].[Foo].
[PK__Foo__98D78B442690946A]), SEEK:([TICDW].[dbo].[Foo].[keycol]=
[@search]) ORDERED FORWARD)

[Erland Sommarskog]

bill (billma...@gmail.com) writes:
> Here's something interesting: The engine appears to use the index,
> even if it has to do an implicit conversion.
>
> So it isn't doing a scan, but that conversion seems to be pretty
> expensive.

When you have

WHERE indexedvarcharcol = @unicodevalue

there are two possible outcomes, depending on the collation of the
varchar column. If the column has a Windows collation, SQL Server will
choose an range seek. This is possible, because in a Windows collation,
the varchar repetoire is a true subset of the Unicode set, and with
the exception of binary collations, the sort order is also the same.
The cost for this range seek is typically a factor 2-3 in my experience.

But if you have an SQL collation, varchar and nvarchar are two different
worlds, and SQL Server will discard seeking the index entirely and you
will get a scan of some sort. Depending on the table size, this can lead
to increase of tenfold, hundredfold or even thousandfold.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx