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

    同样的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"?>
<!--
PWMIS SqlMap Ver 1.1.2 ,2006-11-22,http://www.pwmis.com/SqlMap/
Config by SqlMap Builder,Date:2010/9/19
请在VS的IDE菜单 XML-》架构 里面选择架构文件 SqlMap.xsd,这样直接编辑本文件将就可以有智能提示了。
-->
<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="">
<!--省略N多SQL-MAP脚本.-->
<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"?>
<!--
PWMIS SqlMap Ver 1.1.2 ,2006-11-22,http://www.pwmis.com/SqlMap/
Config by SqlMap Builder,Date:2010/9/19
请在VS的IDE菜单 XML-》架构 里面选择架构文件 SqlMap.xsd,这样直接编辑本文件将就可以有智能提示了。
-->
<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="">
<!--省略N多SQL-MAP脚本.-->
<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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏编程札记

Lucene构建个人搜索引擎解析

简单来说,Lucene提供了一套完整的工具来帮助开发者构建自己的搜索引擎,开发者只需要import Lucene对应的package即可快速地开发构建自己的业务...

2232
来自专栏涂小刚的专栏

Spark SQL 之 Join 实现

如今Spark SQL(Dataset/DataFrame)已经成为Spark应用程序开发的主流,作为开发者,我们有必要了解Join在Spark中是如何组织运行...

3.7K2
来自专栏Java技术栈

MySQL数据库开发的 36 条军规!

来自一线的实战经验,主要针对DBA和后端开发人员,总是在灾难发生后,才想起容灾的重要性;总是在吃过亏后,才记得曾经有人提醒过。文末是详细的视频讲解和PDF下载。...

1605
来自专栏黑泽君的专栏

day44_Oracle学习笔记_03

先去Oracle官网去下载最新版本的sqldeveloper,下载地址:https://www.oracle.com/technetwork/developer...

1112
来自专栏PPV课数据科学社区

【学习】七天搞定SAS(五):数据操作与合并

数据集操作永远是逃不掉的问题,最简单的就是两个数据集的合并——当然不是简简单单的行列添加,按照某一主键或者某些主键合并才是最常用的。在SAS中,要熟悉的就是SE...

39811
来自专栏Java3y

移动商城第七篇【购物车增删改查、提交订单】

把商品加入购物车 接下来我们要做的就是将商品加入到购物车中。我们这次使用的是Cookie来将用户的信息存储起来。那为什么要用cookie呢?? 如果将购物车存储...

1.3K14
来自专栏跟着阿笨一起玩NET

C#常用工具类——Excel操作类

1591
来自专栏技术之路

sql 时间总结

(本贴是从网上找了几个比较好的帖子总合了一下并做了一下修改) 下表列出了 Microsoft® SQL Server™ 识别的日期部分和缩写。 日期部分 缩...

2819
来自专栏大数据架构

Spark SQL / Catalyst 内部原理 与 RBO

从上图可见,无论是直接使用 SQL 语句还是使用 DataFrame,都会经过如下步骤转换成 DAG 对 RDD 的操作

3246
来自专栏Java爬坑系列

【MySQL疑难杂症】如何将树形结构存储在数据库中(方案三 Closure Table)

  今天介绍将树形结构存储在数据库中的第三种方法——终结表(原谅我这生硬的翻译。。)。   继续用上一篇的栗子,下面是要存储的结构图: image.png  ...

6908

扫码关注云+社区

领取腾讯云代金券