sql – 复杂LIKE查询非常慢再次NVARCHAR(450),与VARCHAR(450)相

我在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％) 我会尝试上传图像

解决方法

for (; foundValue == false && Start < (length - 2); Start += 1)
        {
            searchValue = x.Substring(Start,2);
            if (searchValue == compareValue)
                foundValue = true;
        }

在NVARCHAR中只有两倍的字符.

从我自己的测试中,我注意到以下几点：

Table ‘tblVarcharNoIndex’. Scan count 1,logical reads 97,physical
reads 0,read-ahead reads 0,lob logical reads 0,lob physical reads
0,lob read-ahead reads 0.

Table ‘tblNVarcharNoIndex’. Scan count 1,logical reads 189,lob read-ahead reads 0.

逻辑读取意味着比较存储了多少SQL,我们注意到这是超过2x的.我认为在查看实际执行计划时可以看出答案,并注意到估计的行数为56对73,甚至认为最终返回相同数量的行.

然而,查看“客户统计”,显示了您可能注意到的内容：

NVAR    VAR     AVERAGE
Query Profile Statistics                    
  Number of INSERT,DELETE and UPDATE statements        0       0       0.0000
  Rows affected by INSERT,DELETE,or UPDATE statements 0       0       0.0000
  Number of SELECT statements                           2       2       2.0000
  Rows returned by SELECT statements                    306     306     306.0000
  Number of transactions                                0       0       0.0000
Network Statistics                  
  Number of server roundtrips                           1       1       1.0000
  TDS packets sent from client                          1       1       1.0000
  TDS packets received from server                      45      23      34.0000
  Bytes sent from client                                146     144     145.0000
  Bytes received from server                            180799  91692   136245.5000
Time Statistics                 
  Client processing time                                286     94      190.0000
  Total execution time                                  317     156     236.5000
  Wait time on server replies                           31      62      46.5000

注意从服务器接收到的TDS数据包是不同的(记住行的估计是不同的),这不仅占用更多的字节,而且需要时间处理.执行时间约为2倍,处理时间为3倍.

这多少与您的处理器和SQL Server的协议有关？可能有一些或很多(这个查询是运行在一个古老的EDU联想笔记本电脑,Windows 10,DuoCore 1.64Ghz,16GB DDR3).虽然具体细节我不合格回答.

不过,我们可以总结一件事情：SQL Server对行的估计对客户端和发送/接收的数据有影响.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!