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