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]))

 

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

时间: 2024-12-09 16:26:30

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

SQL Server中提前找到隐式转换提升性能的办法

原文:SQL Server中提前找到隐式转换提升性能的办法     http://www.cnblogs.com/shanksgao/p/4254942.html 高兄这篇文章很好的谈论了由于数据隐式转换造成执行计划不准确,从而造成了死锁.那如果在事情出现之前发现了这类潜在的风险岂不是更好?     那么我们来看一个简单的例子,如代码清单1所示.   1: SELECT * 2: FROM HumanResources.Employee 3: WHERE NationalIDNumber = 2

SQL Server中的Forwarded Record计数器影响IO性能的解决方法_MsSql

一.简介      最近在一个客户那里注意到一个计数器很高(Forwarded Records/Sec),伴随着间歇性的磁盘等待队列的波动.本篇文章分享什么是forwarded record,并从原理上谈一谈为什么Forwarded record会造成额外的IO. 二.存放原理     在SQL Server中,当数据是以堆的形式存放时,数据是无序的,所有非聚集索引的指针存放指向物理地址的RID.当数据行中的变长列增长使得原有页无法容纳下数据行时,数据将会移动到新的页中,并在原位置留下一个指向新

SQL Server中的Forwarded Record计数器影响IO性能的解决方法

一.简介 最近在一个客户那里注意到一个计数器很高(Forwarded Records/Sec),伴随着间歇性的磁盘等待队列的波动.本篇文章分享什么是forwarded record,并从原理上谈一谈为什么Forwarded record会造成额外的IO. 二.存放原理 在SQL Server中,当数据是以堆的形式存放时,数据是无序的,所有非聚集索引的指针存放指向物理地址的RID.当数据行中的变长列增长使得原有页无法容纳下数据行时,数据将会移动到新的页中,并在原位置留下一个指向新页的指针,这么做的

Sql Server中如何Float格式转换字符串varchar

SELECT CONVERT(varchar(100), CAST(@testFloat AS decimal(38,2))) SELECT STR(@testFloat, 38, 2) 从Excel中导入到sql2000,有一列"联系方式"变成了float类型,我想转换成nvarchar类型,用下面的语句 select convert(nvarchar(30),convert(int,联系方式)) from employee go //数据溢出,不行! select convert(

sql server中如何实现先分组,然后取每一组的最大值,要取整行的数据,所以不能用max

问题描述 sql server中如何实现先分组,然后取每一组的最大值,要取整行的数据,所以不能用max sql server中如何实现先分组,然后取每一组的最大值,要取整行的数据,所以不能用max 解决方案 http://www.tuicool.com/articles/j6vQzaI 解决方案二: http://www.2cto.com/database/201407/315637.htmlhttp://blog.csdn.net/sz_bdqn/article/details/7259738

sql server中使用T-Sql操作Xml数据

一.前言 SQLServer 2005 引入了一种称为 XML 的本机数据类型.用户可以创建这样的表,它在关系列之外还有一个或多个 XML 类型的列:此外,还允许带有变量和参数.为了更好地支持 XML 模型特征(例如文档顺序和递归结构),XML 值以内部格式存储为大型二进制对象 (BLOB). 用户将一个XML数据存入数据库的时候,可以使用这个XML的字符串,SQL Server会自动的将这个字符串转化为XML类型,并存储到数据库中. 随着SQL Server 对XML字段的支持,相应的,T-S

SQL Server中XML与JSON应用比较

title: SQLServer · 特性分析 · SQL Server中XML与JSON应用比较 author: 石沫 背景 SQL Server是一种强大的数据库引擎,不仅性能卓越,稳定,功能还很强大,SQL Server 2016中已经支持JSON.这让我想到以前工作中经常使用的SQL XML,也对比一下他们几个关键领域的应用方法.这两种SQL特性,在实际的工作中也是常用的功能,特别是JSON在最近发展非常火爆,SQL Server也不落后,很快就在SQL Server2016支持. 广义

SQL Server中行列转换 Pivot UnPivot

原文:SQL Server中行列转换 Pivot UnPivot PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (-) )AS P 完整语法: table_source PIVOT( 聚合函数(value_column) FOR pivot_column IN(<column_list>) )   UNPIVOT用于将列明转为列值(即列转行),在SQL Ser

SQLServer · 特性分析 · SQL Server中XML与JSON应用比较

title: SQLServer · 特性分析 · SQL Server中XML与JSON应用比较 author: 石沫 背景 SQL Server是一种强大的数据库引擎,不仅性能卓越,稳定,功能还很强大,SQL Server 2016中已经支持JSON.这让我想到以前工作中经常使用的SQL XML,也对比一下他们几个关键领域的应用方法.这两种SQL特性,在实际的工作中也是常用的功能,特别是JSON在最近发展非常火爆,SQL Server也不落后,很快就在SQL Server2016支持. 广义