Sql query tunning for nvarchar column

Question

HI team,

SELECT d.[Name],d,[ShortName]

      ,isnull(replace(ValueList, '&', '&'), '') ValueList, 
      ,isnull(replace(ValueName, '&', '&'), '')     FROM Config d (nolock)
join Group g (nolock) on d.valueGroup = g.valuegroup

In above query valuelist and valuename are taking30sec to retrieve data, if they are commented / deleted it is getting in 2sec or less.

those two are nvarchar max coulmns. can u please suggest some ideas to improve performance

thanks in advance

Answer 1

Supposedly the query plans with and without the nvarchar(MAX) columns are not the same. When you don't include the nvarchar(MAX) columns, there may be covering indexs so that SQL Server does not have to go the data pages to retrieve the data.

Add to this that if the nvarchar(MAX) columns are long (as the MAX indicates), they are stored out of row, and they may be more or less fragmented, all contributing to slower access times. You can try to run ALTER INDEX REORGANIZE on the clustered index on the table; this includes LOB compaction.

There is one thing you can do in the query itself:

replace(ValueLit COLLATE Latin1_General_BIN2, '&', '&'), '') `

By switching to a binary collation, you speed up the search for & in the string itself with a factor of 10 or so. But if the main bottleneck is the retrieval of the data, the binary collation will only shave off a few seconds.

I should add that a binary collation is of course case-sensitive, so the above will not replace & or &Amp;, but it safe to assume that all you have is &.

Answer 2

Hi @lakshmi sundari ,

Welcome to the microsoft TSQL Q&A forum!

The efficiency of using nvarchar is relatively low, because all its characters are represented by two bytes.So you can change the nvarchar column to char or varchar column according to the actual situation.

Please refer to the following description of the difference and efficiency of char, varchar, and nvarchar columns:

1.CHAR. CHAR is very convenient to store fixed-length data, and the index on the CHAR field is highly
efficient. For example, if you define char(10), then no matter whether the data you store reaches 10
bytes, it will take up 10 bytes of space.

2.VARCHAR. Stores variable-length data, but the storage efficiency is not as high as that of CHAR. If the
possible value of a field is of variable length, and we only know that it will not exceed 10 characters.Then
it is most effective to define it as VARCHAR(10). The actual length of the VARCHAR type is the actual
length of its value + 1. Why "+1"? This one byte is used to store the actual length used.
In terms of space, varchar is appropriate; in terms of efficiency, char is appropriate, which can be
determined according to actual conditions.

3.NVARCHAR stores characters of Unicode data type. We know that among the characters, English
characters only need one byte to store, but there are many Chinese characters and two bytes are needed
to store. It is easy to cause confusion when English and Chinese characters exist at the same time. The
Unicode character set is to solve this incompatibility of character sets. Its all characters are represented
by two bytes, that is, English characters are also represented by two bytes. The length of nchar and
nvarchar is between 1 and 4000. Compared with char and varchar, nchar and nvarchar can store up to
4000 characters, whether in English or Chinese characters; while char and varchar can store up to 8000
English and 4000 Chinese characters. It can be seen that when using the nchar and nvarchar data types,
there is no need to worry about whether the input characters are English or Chinese, which is more
convenient, but there is some loss in the quantity when storing English.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.