同样的SQL语句在查询分析器执行很快，但是网站上执行超时的诡异问题

最新推荐文章于 2022-02-09 11:50:59 发布

weixin_33726313

最新推荐文章于 2022-02-09 11:50:59 发布

阅读量745

点赞数

文章标签：数据库运维操作系统

同样的SQL语句在查询分析器执行很快，但是网站上执行超时，这个问题以前遇到过，解决办法是重新启动服务器，但过一段时间后（时间长短不一定，一般为一天后），这次又出现了，不能总是重新启动服务器了事吧，决定探个究竟。

首先，打开SQLSERVER 事务探查器，找到那个执行超时的SQL语句：

exec sp_executesql N '
      SELECT a.WorkNo,a.理财经理网点,a.理财经理姓名,a.序号,CAST( ROUND(a.金额/10000,2) as float) 金额
      FROM [GetStatisticsAnalysis_ManagerWorkFeatTop3PM] (
  @trantype  ,
  @manageid  ,
  @startime  ,
  @endtime ,
   @Roleid
  ) a ' ,N ' @trantype nvarchar(200),@manageid nvarchar(38),@startime nvarchar(21),@endtime nvarchar(21),@Roleid nvarchar(38) ' , @trantype = N ' 认购 ' , @manageid = N ' 32800085 ' , @startime = N ' 2010-01-01 ' , @endtime = N ' 2010-12-31 ' , @Roleid = N ' 5BBBBD85-27E4-4679-A010-0076FAD1589F '

怎么看到的都是些 exe sp_executesql 的系统存储过程调用？查阅资料得知，SQL SERVER 会把所有带参数化查询的SQL语句使用sp_executesql来执行，因为它能够分析并缓存查询计划，从而优化查询效率，这也是为什么通常说的“参数化查询比拼接SQL要快”的原因。

将上面的SQL语句再拿到查询分析器里面执行，速度很快，不到1秒就出来了，将它再拿到另外一个.NET写的数据库查询工具程序中执行，却报出了跟网站一样的错误：查询超时！

百思不得其解，网上搜索了一下，果然有人遇到过通用的问题：

http://social.microsoft.com/Forums/zh-CN/visualcshartzhchs/thread/fcdc74d7-0e82-4d34-94c2-d22ba5946d3c

里面有人说：

在sql server 2005里执行存储过程后，消息窗口有这么一句话“警告: 聚合或其他 SET 操作消除了空值。”。

ADO.net可能因为这个警告导致出结果很慢，虽然在sql server里执行没什么问题。

原因是sum里面没有isnull一下。改了一下sql语句就好了。

再看看我们的这个SQL自定义函数GetStatisticsAnalysis_ManagerWorkFeatTop3PM，里面果然有大段的聚合函数：

函数定义

ALTER FUNCTION [ dbo ] . [ GetStatisticsAnalysis_ManagerWorkFeatTop3PM ]
(
-- Add the parameters for the function here
@TradeType varchar ( 200 ),
@WorkNo varchar ( 38 ),
@StartDate varchar ( 21 ),
@EndDate varchar ( 21 ),
@RoleGUId varchar ( 38 )
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here

select * from
(
select ROW_NUMBER() over ( order by 金额 desc ) 序号, * from
(
select a.WorkNo,b.RealName 理财经理姓名,b.NetworkNO 理财经理网点
, case when @TradeType = ' 认购 ' then sum (a.认购金额)
   when @TradeType = ' 申购 ' then sum (a.申购金额)
   when @TradeType = ' 定投 ' then sum (a.定投金额)
   when @TradeType = ' 销售 ' then sum (a.买入金额)
   when @TradeType = ' 赎回 ' then sum (a.卖出金额)
end 金额
from [ WFT_Batch_ManagerWorkFeatDetails ] a inner join ( select * from Tb_Common_User a where a.RoleGUId in ( ' 5BBBBD85-27E4-4679-A010-0076FAD1589F ' , ' 9C2728D4-1E0A-40CD-95AC-6C20029F0871 ' , ' A105F9F8-F9BB-4B68-9426-76E5D10DC1C7 ' )) b on a.WorkNo = b.WorkNo
where a.WorkNo is not null and b.WorkNo is not null and a.jjdm is not null and a.交易日期 >= cast ( @StartDate as datetime ) and a.交易日期 <= cast ( @EndDate as datetime )
group by a.WorkNo,b.RealName ,b.NetworkNO
) a
) a where a.序号 <= 3
union all
select 0 ,a.WorkNo, ' 名下 ' + @TradeType + ' 客户 ' ,b.NetworkNO 理财经理网点
, case when @TradeType = ' 认购 ' then sum (a.认购金额)
   when @TradeType = ' 申购 ' then sum (a.申购金额)
   when @TradeType = ' 定投 ' then sum (a.定投金额)
   when @TradeType = ' 销售 ' then sum (a.买入金额)
   when @TradeType = ' 赎回 ' then sum (a.卖出金额)
end 金额
from [ WFT_Batch_ManagerWorkFeatDetails ] a left join    Tb_Common_User b   on a.WorkNo = b.WorkNo
where a.WorkNo = @WorkNo and a.jjdm is not null and a.交易日期 >= cast ( @StartDate as datetime ) and a.交易日期 <= cast ( @EndDate as datetime )
and a.WorkNo is not null
group by a.WorkNo,b.RealName ,b.NetworkNO
union all
select 99 , '' , ' 全辖平均 ' , '' 理财经理网点,a.金额 / ( select    case when count ( * ) = 0 then 1 else count ( * ) end from Tb_Common_User a
where a.RoleGUId = @RoleGUId ) from (
select    case when @TradeType = ' 认购 ' then sum (a.认购金额)
   when @TradeType = ' 申购 ' then sum (a.申购金额)
   when @TradeType = ' 定投 ' then sum (a.定投金额)
   when @TradeType = ' 销售 ' then sum (a.买入金额)
   when @TradeType = ' 赎回 ' then sum (a.卖出金额)
end 金额
from [ WFT_Batch_ManagerWorkFeatDetails ] a
inner join ( select * from Tb_Common_User a
where a.RoleGUId = @RoleGUId ) b on a.WorkNo = b.WorkNo
where a.WorkNo is not null and a.jjdm is not null    and a.交易日期 >= cast ( @StartDate as datetime ) and a.交易日期 <= cast ( @EndDate as datetime )
) a

)

GO

将sum里面的字段先ISNULL转换下，修改这个SQL自定义函数，保存，再调用这个函数，OK，不超时了！

但是，DBA告诉我，不可以这么做，因为NULL值在业务上有特别的含义，不能随便转换！

没法，只能将函数恢复原样。

(补充:

执行procedure过程，出现“警告：聚合或其它 SET 操作消除了空值”警告

会导致存储过程的结果集无法得到。

使用 set ansi_warnings off 可以屏蔽这个错误。。

在存储过程的结尾再使用 set ansi_warnings on 恢复原来的设置

使用这个方法,可以解决本文标题的问题.

)

再次调用函数，还是没有超时？难道跟这个NULL在聚合函数里面的问题无关？

猜想应该是SQLSERVER将上次的查询结果缓存了，等等看。

第二天，问题又出现了，查询超时，但这次既不能重新启动服务器，也不能修改这个自定义函数，怎么办？

同事帮我在网上搜索了一下，找到这篇文章：

参数化查询比拼接字符串慢的原因

里面说，是参数类型不正确，必须设定为数据库一致的参数类型。

我们的系统使用PDF.NET数据开发框架做的，所以要改这个问题只需要在SQL-MAP配置文件里面修改一下就可以了：

<? xml version="1.0" encoding="utf-8" ?>

< SqlMap EmbedAssemblySource ="FTWebDAL,FTWebDAL.SqlMap.config" >
< Script Type ="SqlServer" Version ="2005" ConnectionString ="Server=192.168.1.2;uid=sa;pwd=sasa;database=XXDB;" >
< CommandClass Name ="StatisticalAnalysisDAL" Class ="StatisticalAnalysisDAL" Description ="" Interface ="" >

< Select CommandName ="GetInfobyTranType" CommandType ="Text" Method ="" Description ="根据交易类型获取详细信息" ResultClass ="DataSet" >
     <![CDATA[
      SELECT a.WorkNo,a.理财经理网点,a.理财经理姓名,a.序号,CAST( ROUND(a.金额/10000,2) as float) 金额
      FROM [GetStatisticsAnalysis_ManagerWorkFeatTop3PM] (
  #trantype:String,String,200#  ,
  #manageid:String,String,38#  ,
  #startime:String,String,21#  ,
  #endtime:String,String,21# ,
   #Roleid:String,String,38#
  ) a order by 序号 asc ]]> </ Select >
</ CommandClass >
</ Script >
</ SqlMap >

将上面的参数类型稍作修改：

<? xml version="1.0" encoding="utf-8" ?>

< SqlMap EmbedAssemblySource ="FTWebDAL,FTWebDAL.SqlMap.config" >
< Script Type ="SqlServer" Version ="2005" ConnectionString ="Server=192.168.1.2;uid=sa;pwd=sasa;database=XXDB;" >
< CommandClass Name ="StatisticalAnalysisDAL" Class ="StatisticalAnalysisDAL" Description ="" Interface ="" >

< Select CommandName ="GetInfobyTranType" CommandType ="Text" Method ="" Description ="根据交易类型获取详细信息" ResultClass ="DataSet" >
     <![CDATA[
      SELECT a.WorkNo,a.理财经理网点,a.理财经理姓名,a.序号,CAST( ROUND(a.金额/10000,2) as float) 金额
      FROM [GetStatisticsAnalysis_ManagerWorkFeatTop3PM] (
  #trantype:String, AnsiString ,200#  ,
  #manageid:String, AnsiString ,38#  ,
  #startime:String, AnsiString ,21#  ,
  #endtime:String, AnsiString ,21# ,
   #Roleid:String, AnsiString ,38#
  ) a order by 序号 asc ]]> </ Select >
</ CommandClass >
</ Script >
</ SqlMap >

把第二个String参数修改成AnsiString即可，对于SQL-MAP而言，参数格式是：

#ParameterName:Type,DbType,Length#，

所以相当于修改了DbType的类型。

保存配置文件，重新编译，OK，问题解决！！

为什么将DbType.String 修改成DbType.AnsiString就可以大大提高查询效率呢？

查询了资料，有下面的说法：

正如所述，ansistring是存放非unicode字符，而通常情况下，中文也是以ansi字符方式来存放的。

unicode的关键是里面有 0 这个代码，而ansi里面是以 0 表示结束。同样，unicode里需要 0 0 （连续的两个0）来表示结束。

DbType.AnsiString指明了是ansi字符集，中间不会在进行转换。

DbType.String没有指明字符集，输入的内容会根据数据库来转换（如连接时用的字符集、表的字符集等）

-----------------------------------------------------

么数据库里面的字符集默认使用系统的字符集，也就是ANSI字符集，如果是中文操作系统，那么它就是GB2312格式的。

显然，GB2312不是Unicode字符集，但我们的程序里面默认的String类型是Unicode类型的，因此会在程序的字符集和数据库的字符集直接做转换，有可能导致数据库查询效率大大降低。

----------------------------------------------------

另外也有人说，数据库字段是varchar类型，程序中设置成DbType.String奇慢，但是设置成DbType.AnsiString将很快：

使用DbParameter傳遞參數撈SQL Server資料速度異常的慢

http://adyhpq.blog.163.com/blog/static/3866700201062331034769/

c#Dbtype与SQL dbtype一一对应关系，提高效率关键

http://blog.csdn.net/luofuxian/archive/2010/11/02/5981539.aspx

DbType:SqlDbType
AnsiString:VarChar
Binary:VarBinary
Byte:TinyInt
Boolean:Bit
Currency:Money
Date:DateTime
DateTime:DateTime
Decimal:Decimal
Double:Float
Guid:UniqueIdentifier
Int16:SmallInt
Int32:Int
Int64:BigInt
Object:Variant
Single:Real
String:NVarChar
Time:DateTime
AnsiStringFixedLength:Char
StringFixedLength:NChar
Xml:Xml
DateTime2:DateTime2
DateTimeOffset:DateTimeOffset

============================================

还有一种说法，可能跟SQLSERVER 2005的一个Bug有关：

FIX：系統效能可能很慢時應用程式送出許多查詢中針對使用簡單的參數化的 SQL Server 2005 資料庫

http://support.microsoft.com/kb/920206/zh-tw

weixin_33726313

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
同样的SQL语句在查询分析器执行很快，但是网站上执行超时的诡异问题

同样的SQL语句在查询分析器执行很快，但是网站上执行超时，这个问题以前遇到过，解决办法是重新启动服务器，但过一段时间后（时间长短不一定，一般为一天后），这次又出现了，不能总是重新启动服务器了事吧，决定探个究竟。首先，打开SQLSERVER 事务探查器，找到那个执行超时的SQL语句：exec sp_executesql N' SELECT a.Work...
复制链接

扫一扫