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 '
在sql server 2005里执行存储过程后,消息窗口有这么一句话“警告: 聚合或其他 SET 操作消除了空值。”。
ADO.net可能因为这个警告导致出结果很慢,虽然在sql server里执行没什么问题。
原因是sum里面没有isnull一下。改了一下sql语句就好了。
再看看我们的这个SQL自定义函数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配置文件里面修改一下就可以了:
<!--
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 >
将上面的参数类型稍作修改:
<!--
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/
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