sql function执行效率

[huangwenquan123]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[getMID](@MID int)
returns varchar(500)
as
begin
declare @MIDS varchar(200)
set @MIDS=''
;with cte as
(
	select MID,MPID from Member_Tbl where MID=@MID
	union all
	select m.MID,m.MPID from cte inner join Member_Tbl m on cte.MID=m.MPID
)
select @MIDS=@MIDS+','+convert(varchar(100),MID) from cte
set @MIDS=stuff(@MIDS,1,1,'')
return @MIDS
end

--执行--
select * from Member_Tbl where charindex(','+convert(varchar(200),MID)+',',','+dbo.getMID(1)+',')>0

sql函数的执行效率怎么样呢？

[huangwenquan123]

sql版的大哥大姐哪去了呢？

[叶子]

如果要用那个分页存储过程的话，就需要用函数封装成条件。

但是用;with xx as的话，可以不用固定的分页存储过程。

例如

create proc procname
(
@pageindex INT, --1
@pagesize INT,--2
@MID  int
)
AS
begin
	declare @t table (MID int,MPID int)
	insert into @t
	select 1,0 union all
	select 2,1 union all
	select 3,2 union all
	select 4,0 union all
	select 5,0

	DECLARE @i INT;SET @i=1 --参数自己改
	;WITH maco AS
	(select * from @t WHERE mid=@i UNION ALL
	SELECT a.* FROM @t a,maco WHERE a.MPID=maco.MID)

    SELECT * FROM 
    (
	SELECT ROW_NUMBER() OVER (ORDER BY mid) AS rowid, * FROM maco
	) bb
	WHERE rowid 
	BETWEEN (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize
END

EXEC procname 1,2,1
/*
rowid                MID         MPID
-------------------- ----------- -----------
1                    1           0
2                    2           1
*/
EXEC procname 2,2,1
/*
rowid                MID         MPID
-------------------- ----------- -----------
3                    3           2
*/

[huangwenquan123]

[Quote=引用 10 楼 maco_wang 的回复:]
如果要用那个分页存储过程的话，就需要用函数封装成条件。

但是用;with xx as的话，可以不用固定的分页存储过程。

例如

SQL code

create proc procname
(
@pageindex INT, --1
@pagesize INT,--2
@MID int
)
AS
begin
declare @t table (MID ……
[/Quote]可是叶子，那个存储过程分页都已经封装好了，就只能输入表名，字段名，条件语句，所以我才把他写成函数的！可是函数效率又高，叶子在帮我支个招！

[huangwenquan123]

[Quote=引用 10 楼 maco_wang 的回复:]
如果要用那个分页存储过程的话，就需要用函数封装成条件。

但是用;with xx as的话，可以不用固定的分页存储过程。

例如

SQL code

create proc procname
(
@pageindex INT, --1
@pagesize INT,--2
@MID int
)
AS
begin
declare @t table (MID ……
[/Quote]漏看了第一句，看来只能用函数封装成条件了

[叶子]

分页存储过程大多是针对单表的，如果要做表里面的处理可以写到视图中，但是视图不支持参数的。
你用的是哪个分页存储过程？表名处可以动态嵌套吧？

[昵称被占用了]

[Quote=引用 3 楼 huangwenquan123 的回复:]
引用 1 楼 dawugui 的回复:
sql函数的执行效率不高.


那我上面那个语句该怎么改呢？
Member_Tbl这个表的数据后期还蛮多的！
[/Quote]
不久的将来，就是灾难
如果不怕2012，那就继续这个思路来做吧

[huangwenquan123]

可以 表名

string tablename = "Orders_Tbl o inner join Products_Tbl p on o.ID=p.ID"

这样

[叶子]

建议你还是用我写的存储过程进行扩展一下

tablename这个位置不能放入with表达式

[叶子]

[Quote=引用 14 楼 haiwer 的回复:]
引用 3 楼 huangwenquan123 的回复:
引用 1 楼 dawugui 的回复:
sql函数的执行效率不高.


那我上面那个语句该怎么改呢？
Member_Tbl这个表的数据后期还蛮多的！

不久的将来，就是灾难
如果不怕2012，那就继续这个思路来做吧
[/Quote]
海爷有什么好办法？

[昵称被占用了]

你的思路不能换吗？
你现在是找下级，而下级的量是没法控制的
如果换成找上级，这个量是可以控制的，最多的层数就是最大的返回。
没仔细看，大概觉得问题严重，不知道你们怎么讨论到分页了，你们继续。

[昵称被占用了]

总的来说，SQL的自定义函数效率是没法保证的，躲不开的时候应该设法用CLR函数来代替或者只有在确定函数处理的数据量能控制在很小的范围（最多就只有几千行）的情况下才使用。

[快溜]

递归分页，海爷担心是有道理的，等世界末日。

[叶子]

[Quote=引用 19 楼 haiwer 的回复:]
你的思路不能换吗？
你现在是找下级，而下级的量是没法控制的
如果换成找上级，这个量是可以控制的，最多的层数就是最大的返回。
没仔细看，大概觉得问题严重，不知道你们怎么讨论到分页了，你们继续。
[/Quote]
海爷的意思是递归的效率本身就不好，数据量大的时候得到数据的时候就容易出问题？

[叶子]

问题是数据量大，又有上下级关系，又要分页，怎么弄才能高效呢？

[huangwenquan123]

[Quote=引用 23 楼 maco_wang 的回复:]
问题是数据量大，又有上下级关系，又要分页，怎么弄才能高效呢？
[/Quote]就是这个意思，本身页面就是一个分页的，然后上级根据条件找出下级，我也很悲剧！

[子夜__]

来学习学习SQL怎么样

[huangwenquan123]

[Quote=引用 26 楼 wxr0323 的回复:]
来学习学习SQL怎么样
[/Quote]sql版大牛好多！

[叶子]

数据量大了，使用递归效率就差了，这个貌似不是sql的问题，是个通病吧

[昵称被占用了]

如果知道数据量大，需求是需要这样查询的，可以在表结构上避免使用递归算法。

比如，树形结构表增加一个冗余字符字段path，并建立索引，那一个节点（比如path=1.1.1）的所有下级都符合 path like '1.1.1.%',这个like虽然效率不太高，但是还是使用索引seek的，算是一个效率和一致性折中的方法。

[huangwenquan123]

我恨我自己的眼睛，其实，还有个深度的字段...就像海爷说的那样：0，1，2
根据那个就可以了。
我对不起大家。浪费大家时间！
结帖