|
|
Posted by Erland Sommarskog on 11/22/06 11:19
Daniel Smedegaard Buus (danielbuus@gmail.com) writes:
> Either way, this is how a query from the mssqlclient adapter might look:
>
> SELECT TOP 1 * FROM Item WHERE (Item.Itemnumber = N'45783745')
>
> Response time the first couple of times was upwards of 20+ seconds, after
> the sql server has "awaken from its slumber", it's roughly 4 seconds.
>
> Omitting the "N" from the WHERE clause, response time is in milliseconds
Obviously Item.Itemnumber is a varchar column and is indexed.
When two different data types meet, SQL Server applies a strict data-type
precedence, and converts the type with lower precedence to the higher.
The data type of a string literal with a preceeding N is nvarchar. nvarchar
has higher precendence than varchar. Thus, the varchar column is converted
to nvarchar. However, the index is built on a varchar value, not an nvarchar
value, and can therefore not be seeked, only scan.
The remedy is to remove the N as you have noticed. If I understood this
correctly, the code above is generated, and if you do not have control
over how it's generated, the other alternative is to change the
data type of the column to nvarchar.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|