473,320 Members | 1,857 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Severe performance hit with NCHAR queries

Hey there :)

Sorry if I'm asking a dumb question here, but I'm still quite new to MS SQL,
so this problem might appear larger to me than it really is.

I'm trying to create a performance test environment for a Ruby on Rails and
Mongrel setup with an MS SQL Server 2000.

The adapter, mssqlclient, uses some kind of "conversion" for unicode, here's
a quote from the homepage:

"Automatically translate from proper UTF-16LE nvarchar fields in the
database to UTF-8 Ruby Strings you can display in your application"

As far as the local DB designer knows, we're not using UTF16-LE nvarchar
fields, unless it's something that happens implicitly.

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 (as
one would expect, regardless of the fact that there's currently >2.5
million items in the table).

Any tips on how to resolve this? Is the SQL statement bad, or is it a
question of configuring SQL Server correctly?

Thanks in advance for any help,
Daniel Buus :)

--
http://www.rhesusb.dk
Nov 22 '06 #1
2 2203
Daniel Smedegaard Buus (da********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 22 '06 #2
Erland Sommarskog wrote:
Daniel Smedegaard Buus (da********@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
Nov 22 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Andy Tran | last post by:
I built a system using mysql innodb to archive SMS messages but the innodb databases are not keeping up with the number of SMS messages coming in. I'm looking for performance of 200 msgs/sec where...
12
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance speed of this SP. In the next couple of weeks I...
2
by: Lauren Quantrell | last post by:
I'm using replace in a stored procedure to eliminate carriage returns a user might have entered in an Access field. The problem is that even though it seems to strip out the carriage return I...
4
by: Bob Alston | last post by:
Some more, rather specific Access performance questions. IN a split front-end & back-end Access/Jet ONLY LAN situation, and with all query criteria fields and join fields indexed: 1. Is is...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.