自定义函数在SQL语句中,记录多时运行就很慢,有时会超时退出,请问怎么处理?

Question

我的程序中要动态的去算每一列的值,其中用到自定义函数,记录多时运行就很慢,有时会超时退出,请问怎么处理?
例自定义函数如下:
CREATE function charge_total(@bh varchar(30),@type varchar(60),@mx varchar(50))
returns decimal(9,2) --注意返回值的长度
as 
begin
    declare @re decimal(9,2) --
    set @re=0
    select @re=@re+total from m_chargelist where rdno=@bh and type=@type and charge=@mx 
return @re
end
我在SQL的语句中可能会多次出现这个函数:select rdno,client,dbo.charge_total(rdno,'收','运费'),dbo.charge_total(rdno,'收','杂费'),dbo.charge_total(rdno,'收','其他)..from.....
这样的语句,当m_chargelist记录数多了后,运行这样的语句就会超时等,出错请问有好的解决方法吗?谢了!

Answer 1

CREATE function charge_total(@bh varchar(30),@type varchar(60),@mx varchar(50)) 
returns decimal(9,2) --注意返回值的长度 
as 
begin 
    declare @re decimal(9,2) 
    select @re=sum(total) from m_chargelist where rdno=@bh and type=@type and charge=@mx 
return @re 
end 
go

Answer 2

这样的语句,当m_chargelist记录数多了后,运行这样的语句就会超时等,出错请问有好的解决方法吗?谢了!------------
如果只是查询的话，一次不要取那么多记录，做个分页处理。

Answer 3

这意味着语句每扫一条记录，都会多次（多少次，要看你调用多少次函数）再从表中进行+(你自己的写法或聚合熊的写法)这样，怎么效率都不会高的。至于怎么写效率高，我希望看到你的语句中的 select rdno,client,dob..... from 这里的表名

Answer 4

CREATE INDEX IX_AllNeededCol ON m_chargelist(rdn,type,charge,total)
GO

Answer 5

另外，我想知道你的sqlserver版本。

Answer 6

 SQLCommand cmd;
cmd.CommandTimeOut=0;

Answer 7

只能试着给m_chargelist 表的rdno,type,charge (,total) 加个索引试试喽 

Answer 8

SELECT  rdno,
        client,
        收运费 = SUM(CASE WHEN type = '收'
                            AND charge = '运费' THEN total
                       ELSE 0
                  END),
        收杂费 = SUM(CASE WHEN type = '收'
                            AND charge = '杂费' THEN total
                       ELSE 0
                  END),
        收其他 = SUM(CASE WHEN type = '收'
                            AND charge = '其他' THEN total
                       ELSE 0
                  END)
FROM    m_chargelist
GROUP BY rdno,
        client

Answer 9

1. 尽量避免. 用户定义函数是一个独立体, 查询优化器没办法拿它里面的东西结合你的查询做优化
2. 尽量使用 WITH SCHEMABINDING. 这可以造就确定性函数, 确定性函数的好处是, 在一个查询中, 如果函数的输入参数是同一个, 则函数不会重复调用
3. 在可能的情况下, 把函数定义为列的计算值(2005及之后的版本指定 PERSISTED 选项, 2005之前的版本在该列上建立索引), 或者把它放在索引视图中, 这样函数的计算结果是持久化的, 引用的时候不用反复计算(不过要确定性函数才行, 另外, 当然会对数据变更速度造成影响)

Answer 10

问你 from 后面接的是什么，其实也是想写9楼鸟儿这类似的语句，想看看能不能改写成这样。

Answer 11

不好意思,下午有点事.我的SQL版本是2000,因为我做的是动态的SQL语句,所以SQL的列是动态的,不能固定,select rdno,client,dbo.charge_total(rdno,'收','运费'),dbo.charge_total(rdno,'收','杂费'),dbo.charge_total(rdno,'收','其他)..(运态的)...  from  m_operation(业务主表) where client='aaaaa'等条件另外SQL语句可能还有其他自定义函数,这样做的目的是为了让客户可以自定义列的内容,如自己选择费用名称(运费,杂费,其他,)等9楼的做法如果列是固定的就可以,但我的列是客户可以自定义的,自己加减列的,

Answer 12

既然动态，就用动态语句吧declare @sql varchar(8000)
set @sql='SELECT  rdno,client'
select @sql=@sql+',['+type+']=SUM(CASE WHEN type = '''+type+''' AND charge = '''+charge+''' THEN total ELSE 0 END)'
from (select distinct type,charge from m_chargelist) t
exec(@sql+' FROM m_chargelist GROUP BY rdno, client')

Answer 13

回14楼,你这种SQL,在程序中该怎么读取exec(@sql+' FROM m_chargelist GROUP BY rdno, client')的值,请详细说一下!