|
|
Posted by Daniel Smedegaard Buus on 11/22/06 11:30
Erland Sommarskog wrote:
> 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.
>
>
Hi Erland, thanks for your reply :)
Well, the scanning would explain the slugginess, then! I took your first
advice, and looked into the source code for the adapter (thank god for open
source, eh?), found the problematic code section, and killed off the N,
seeing as how we don't need it anyway. Selects are very snappy now.
Only problem now is an "System.IndexOutOfRangeException" thrown by Ruby, but
that has nothing to do with MS SQL, so I'll take that elsewhere ;)
Thanks again!
Daniel
--
http://www.rhesusb.dk
Navigation:
[Reply to this message]
|