我在Microsoft sql Server 2014上有一个测试版.
我有2个表.两者都具有ID字段(标识(1,1)和值字段(VARCHAR(450)或NVARCHAR(450)).它们都具有相同的1 000 000个随机生成的条目.

这些表命名为tblVarCharNoIndex和tblNVarCharNoIndex(所以没有索引,行为几乎相同,如果我使用索引).

现在,我执行以下查询测试持续时间(一次在VARCHAR上;一次在NVARCHAR上)

SELECT * FROM tblVarcharNoIndex WHERE Value LIKE '%ab%'
SELECT * FROM tblNVarcharNoIndex WHERE Value LIKE '%ab%'

执行时间截然不同. VARCHAR表需要1540ms,NVARCHAR表上需要8630 ms,所以NVARCHAR需要5xx的时间.

我明白,NVARCHAR具有性能影响,因为它需要2个字节来存储,这完全是有道理的.但是,我无法解释表现恶化了500％,这对我来说没有意义

我希望,你们中的一个可以给我更好的洞察这个行为.

感谢Advace

crazy_crank

根据请求,这里有更多的数据.

查询表创建

CREATE TABLE [dbo].[tblVarcharNoIndex](
    [Id] [int] IDENTITY(1,1) NOT NULL,[Value] [varchar](450) NOT NULL,CONSTRAINT [PK_tblVarcharNoIndex] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[tblNVarcharNoIndex](
    [Id] [int] IDENTITY(1,[Value] [nvarchar](450) NOT NULL,CONSTRAINT [PK_tblNVarcharNoIndex] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

查询值生成

DECLARE @cnt INT = 0;
DECLARE @entries INT = 1000000 --1'000'000;
DECLARE @maxLength INT = 450;
DECLARE @minLength INT = 50;
DECLARE @value VARCHAR(450)
DECLARE @length INT

WHILE @cnt < @entries
BEGIN
    SELECT @value = ''
    SET @length = @minLength + CAST(RAND() * (@maxLength - @minLength) as INT)
    WHILE @length <> 0
    BEGIN
        SELECT @value = @value + CHAR(CAST(RAND() * 96 + 32 as INT))
        SET @length = @length - 1
    END

    INSERT INTO tblBase(Value,NValue) VALUES (@value,@value)

    SET @cnt = @cnt + 1;

END;

(值从tblBase以后复制)

LIKE查询有问题

DECLARE @start DATETIME
DECLARE @end DATETIME
DECLARE @testname NVARCHAR(100) = 'INSERT FROM other table'

--VARCHAR No Index
PRINT 'starting ''' + @testname + ''' on VARCHAR (No Index)'
SET @start = GETDATE()

SELECT * FROM tblVarcharNoIndex WHERE Value LIKE '%ab%' --This takes 1540ms

SET @end = GETDATE()
PRINT '-- finished ''' + @testname + ''' on VARCHAR (No Index)'
PRINT '-- Duration ' + CAST(DATEDIFF(mcs,@start,@end) AS VARCHAR(100)) + ' microseconds'


--NVARCHAR No Index
PRINT 'starting ''' + @testname + ''' on NVARCHAR (No Index)'
SET @start = GETDATE()

SELECT * FROM tblNVarcharNoIndex WHERE Value LIKE '%ab%' --This takes 8630ms

SET @end = GETDATE()
PRINT '-- finished ''' + @testname + ''' on NVARCHAR (No Index)'
PRINT '-- Duration ' + CAST(DATEDIFF(mcs,@end) AS VARCHAR(100)) + ' microseconds'

执行计划
这两个查询的执行计划看起来完全相同(我现在无法上传图像,但它真的很简单)：

SELECT(0％)< ---并行(聚集流)(3％)< ---聚集索引扫描ON主键(97％) 我会尝试上传图像