select dbo.fun(icode) nid from t
dbo.fun是自定义函数,表t有上千条数据,结果超时,是因为执行了函数才超时的,
请问该如何解决?
函数如下:
ALTER FUNCTION fun
(
@icode nvarchar(30)
)
RETURNS int
AS
BEGIN
declare @tb table(nid int identity(1,1),icode nvarchar(30),OneMonthPer decimal(18,4))
insert into @tb select icode,OneMonthPer from NT_Insurance_NetVTbl order by OneMonthPer desc
declare @nid int,@nid2 float,@count int
select @count=count(*) from @tb
select @nid=nid from @tb where icode=@icode
set @nid2=cast(@nid as float)/(@count/10)
if(@nid2<=1) return 1
else if(@nid2<=2) return 2
else if(@nid2<=3) return 3
else if(@nid2<=4) return 4
else if(@nid2<=5) return 5
else if(@nid2<=6) return 6
else if(@nid2<=7) return 7
else if(@nid2<=8) return 8
else if(@nid2<=9) return 9
else if(@nid2<=10) return 10 return 1
END
dbo.fun是自定义函数,表t有上千条数据,结果超时,是因为执行了函数才超时的,
请问该如何解决?
函数如下:
ALTER FUNCTION fun
(
@icode nvarchar(30)
)
RETURNS int
AS
BEGIN
declare @tb table(nid int identity(1,1),icode nvarchar(30),OneMonthPer decimal(18,4))
insert into @tb select icode,OneMonthPer from NT_Insurance_NetVTbl order by OneMonthPer desc
declare @nid int,@nid2 float,@count int
select @count=count(*) from @tb
select @nid=nid from @tb where icode=@icode
set @nid2=cast(@nid as float)/(@count/10)
if(@nid2<=1) return 1
else if(@nid2<=2) return 2
else if(@nid2<=3) return 3
else if(@nid2<=4) return 4
else if(@nid2<=5) return 5
else if(@nid2<=6) return 6
else if(@nid2<=7) return 7
else if(@nid2<=8) return 8
else if(@nid2<=9) return 9
else if(@nid2<=10) return 10 return 1
END
解决方案 »
- 急! 我想问下,我的数据库常常出现一致性数据错误
- 哎,现在的图书质量很差
- 菜鸟触发器问题,谢谢回答
- 'where' 附近有语法错误
- sql2000能否通过企业管理器连接上另一台机的sql2005并且做复制?
- 部门拥有城市的表的设计
- 如何把dbf数据库导到sql里面,看清是dbf数据库,而不是foxpro的一个表文件!!!
- 急急!建表时表名前面带#号的表的用法
- 利用DELPHI开发数据库,怎么样在其中建立视图和利用触发器编程?请各位高手指教!
- 安装oracle personal edition,为什么ORA-12541: TNS:no listener?
- 在select查询中模式名+表名+“*”可以用吗?
- 删除取唯一值的约束的问题谢谢!
--select dbo.fun(icode) nid from t
ALTER FUNCTION fun
(
@icode nvarchar(30)
)
RETURNS int
AS
BEGIN
--declare @tb table(nid int identity(1,1),icode nvarchar(30),OneMonthPer decimal(18,4))
--insert into @tb select icode,OneMonthPer from NT_Insurance_NetVTbl order by OneMonthPer desc
declare @nid int,@nid2 float,@count int --select @count=count(*) from @tb
select @count=count(*) from NT_Insurance_NetVTbl --不需要零时表
-- select @nid=nid from @tb where icode=@icode
select @nid=nid from NT_Insurance_NetVTbl where icode=@icode --不需要零时表
set @nid2=cast(@nid as float)/(@count/10)
if(@nid2 <=1) return 1
else if(@nid2 <=2) return 2
else if(@nid2 <=3) return 3
else if(@nid2 <=4) return 4
else if(@nid2 <=5) return 5
else if(@nid2 <=6) return 6
else if(@nid2 <=7) return 7
else if(@nid2 <=8) return 8
else if(@nid2 <=9) return 9
else if(@nid2 <=10) return 10 return 1
END
declare @tb table(nid int identity(1,1),icode nvarchar(30),OneMonthPer decimal(18,4))
insert into @tb select icode,OneMonthPer from NT_Insurance_NetVTbl order by OneMonthPer desc
2.将这个总数作为函数的一个参数传放
select @count=count(*) from @tb
ALTER FUNCTION fun
(
@icode nvarchar(30)
)
RETURNS int
AS
BEGIN
declare @nid int,@nid2 float,@count int
select @count=count(*) from NT_Insurance_NetVTbl
select @nid=count(*) from NT_Insurance_NetVTbl
where OneMonthPer>=(select OneMonthPer from NT_Insurance_NetVTbl where icode=@icode)
return ceiling(@nid*10.0/@count)
END
回树上的鸟儿,用永久表是否会不灵活?
你这个都没有筛选条件,可以直接在函数里返回一个表,,,
不要用function了,NT_Insurance_NetVTbl里数据量这么大,还是先按照icode做成合适的数据集,再 和t表连接处理,要不然执行一次函数就要查询NT_Insurance_NetVTbl,不慢才怪
RETURNS @t table(icode nvarchar(30), nid int)
AS
BEGIN
declare @tb table(nid int identity(1,1),icode nvarchar(30))
insert into @tb select icode from NT_Insurance_NetVTbl order by OneMonthPer desc
declare @nid int,@nid2 float,@count int
select @count=count(*) from NT_Insurance_NetVTbl
insert into @t
select t.icode, nid=ceiling(tb.nid*10.0/@count)
from t join @tb tb on t.icode=tb.icode return
END
GOselect * from dbo.fn1() t