Sql query tunning for nvarchar column

lakshmi sundari 21 Reputation points
2021-04-29T22:03:49.21+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,324 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,522 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 99,371 Reputation points MVP
    2021-04-30T21:14:39.847+00:00

    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 &.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-04-30T01:20:58.427+00:00

    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.

    0 comments No comments