SQL Server 中VARCHAR(MAX)变量赋值引起的性能问题。

案例环境:

          操作系统版本 : Windows Server 2008 R2 Standard  SP1

          数据库版本   :  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

案例介绍:

 

由于不能将生产环境的代码和数据贴上来,所以我构造了下面一个小案例,当然没法和生产环境的案例一致。只能是接近而已。但是足以反映问题本质就足够了。

DROP TABLE ProductPrice; 
 
GO 
 
CREATE TABLE ProductPrice 
 
( 
 
ProductName VARCHAR(14), 
 
Sequence INT , 
 
ProductPrice FLOAT 
 
) 
 
GO 
 

 

构造8000条测试数据,然后将数据插入临时表#tmp(其实完全可以不用临时表,只因为生产环境也是临时表,故模拟接近案例环境)

DECLARE @index INT =1;
DECLARE @subindex INT;
 
 WHILE @index <= 800
 BEGIN
 SET @subindex = 1;
 WHILE @subindex <=10
  BEGIN 
   INSERT INTO ProductPrice
    SELECT ‘product‘ + convert(varchar,@index), @subindex, rand()*1000;
 
   SET @subindex = @subindex +1;
  END;
 
  SET @index = @index +1;
END
 
 
 
SELECT * INTO #tmp FROM ProductPrice;
GO

 

本来开发人人员也许是要使用动态SQL语句获取下面这样一段SQL语句(随意构造小例子,形似神不似)

 

DECLARE @sqlText NVARCHAR(MAX) =‘‘; 
  
SELECT @sqlText=@sqlText+ quotename(productname)+
    ‘=CAST(MAX(CASE WHEN [productname]=‘+QUOTENAME(productname,‘‘‘‘)
      +‘ THEN [productPrice] END) AS VARCHAR)‘ 
FROM #tmp 
GROUP BY ProductName 

SELECT datalength(@sqlText);

 

 

 

但是由于疏忽或是对动态SQL不了解,写成了这样一个SQL语句,结果执行时间一下子飚增到7分多钟。

 

DECLARE @sqlText NVARCHAR(MAX) =‘‘; 
  
SELECT @sqlText=@sqlText+ quotename(productname)+
    ‘=CAST(MAX(CASE WHEN [productname]=‘+QUOTENAME(productname,‘‘‘‘)
      +‘ THEN [productPrice] END) AS VARCHAR)‘ 
FROM #tmp ; 
SELECT datalength(@sqlText);

 

看来SQL对于处理非常长的字符串对象有一定的性能问题,于是为了验证我的想法,我又构造了下面一个例子。创建临时表#tmp,数据来源于 sys.all_columns

 

DROP TABLE #tmp;
GO
SELECT * INTO #tmp FROM sys.all_columns;
GO
 
 
7364 行受影响)

然后我们来看一下下面SQL语句

DECLARE @output NVARCHAR(MAX)
SELECT @output=ISNULL(@output,‘‘) + QUOTENAME(name) + REPLICATE(‘it is only a test ‘, 200)
FROM #tmp

那么我们来看看这条SQL的执行计划,如下所示,很普通的执行计划,看不出有啥特别之处。但是执行性能那叫一个糟糕透顶!

SET SHOWPLAN_ALL ON;
 
GO
 
DECLARE @output NVARCHAR(MAX)
 
SELECT @output=ISNULL(@output,‘‘) + QUOTENAME(name) + REPLICATE(‘it is only a test ‘, 200)
 
FROM #tmp 

StmtText的内容,如下所示:

DECLARE @output NVARCHAR(MAX)

SELECT @output=ISNULL(@output,‘‘) + QUOTENAME(name) + REPLICATE(‘it is only a test ‘, 200)

FROM #tmp

  |--Compute Scalar(DEFINE:([Expr1004]=isnull([@output],CONVERT_IMPLICIT(nvarchar(max),‘‘,0))+quotename([tempdb].[dbo].[#tmp].[name])+N‘it is only a test it is only a test it is only a test it is only a test it is only a test it is only a test it is only a test it is only a test it is only a test it is only a test it is only a test it is only a test it is only a test it is onl‘))

       |--Table Scan(OBJECT:([tempdb].[dbo].[#tmp]))

 

虽然能理解处理大对象需要很多资源,会产生一定的性能问题,但是执行时间这么长,还是让我觉得有点不可思议,但是又不清楚具体原因!

SQL Server 中VARCHAR(MAX)变量赋值引起的性能问题。,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。