巨难的问题,微软MS SQL的巨大BUG!进者有分!

[三断笛]

好吧,如果你进来了,我承认我欺骗了你,其实不是微软的BUG,只是我不懂而已,希望你能帮助我,同时也希望你能耐心看完我的帖子,文字会多一点,但愿这个帖子也能给你一点思考.

废话不多说,是写样的,我写了个SPLIT函数,功能当然不用多说,用来切割字符串的.切割完后返回一个行集.(您先别急着贴代码,我知道网上有好多这个代码,我也复制过了,但那不是我的问题.)这个函数在我的系统里执行极为频繁,所以我要提高这个函数的性能,所以我不希望用多语表值函数,因为我担心更新表变量会消耗性能,在实际的执行跟踪中,发现表更新占的资源挺多,所以我打算用内联表值函数,代码如下:

	WITH cte1 AS(SELECT case when left(@Expression,len(@Delimiter))=@Delimiter then '' else @Delimiter end +@Expression+
						case when right(@Expression ,len(@Delimiter))=@Delimiter  then '' else @Delimiter end AS Expression),
	--构建自然数表
	L0 AS(SELECT 1 AS n UNION ALL SELECT 1),
	L1 AS(SELECT 1 AS n FROM L0 a,L0 b),
	L2 AS(SELECT 1 AS n FROM L1 a,L1 b),
	L3 AS(SELECT 1 AS n FROM L2 a,L2 b),
	L4 AS(SELECT 1 AS n FROM L3 a,L3 b),
	L5 AS(SELECT 1 AS n FROM L4 a,L4 b),
	cte2 AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS n FROM L5)
	/*SELECT SUBSTRING(@Delimiter+@Expression+@Delimiter,n+1,
	CHARINDEX(@Delimiter,@Delimiter+@Expression+@Delimiter,n+1) -n-1  ) AS list 
	FROM   cte2 n
	WHERE SUBSTRING(@Delimiter+@Expression+@Delimiter,n,1)=@Delimiter
	AND n.n<LEN(@Delimiter+@Expression+@Delimiter)
	AND isnull(@Expression,'')<>''
	AND ISNULL(@Delimiter,'')<>''*/
	--分隔字符串
	SELECT SUBSTRING(b.Expression,n+1,CHARINDEX(@Delimiter,b.Expression,n+1) -n-1  ) AS list 
	FROM   cte2 n,cte1 b
	WHERE SUBSTRING(b.Expression,n,1)=@Delimiter
	AND n.n<LEN(b.Expression)
	AND isnull(@Expression,'')<>''
	AND ISNULL(@Delimiter,'')<>''
	AND SUBSTRING(b.Expression,n+1,CHARINDEX(@Delimiter,b.Expression,n+1) -n-1  )<>''
 )

然后我进行了测试,

SELECT * from dbo.[Split]('/1/2/3/4/6/3/3/2/6/23/42/23/23/','/') 

执行成功,性能非常好,然后我继续测试,我希望取出其中一个元素:

SELECT * from dbo.[Split]('/1/2/3/4/6/3/3/2/6/23/42/23/23/','/') WHERE list='1'
消息 536，级别 16，状态 5，第 1 行
传递到 SUBSTRING 函数的长度参数无效。

这次却执行失败了!然后我尝试其他的过滤条件,均告失败.
再接着,我尝试把这个"高性能"的函数与其他表进行Apply运行,效率却出奇地慢!代码如下:

   		  CREATE TABLE testTable (DATA VARCHAR(200))
   		  INSERT INTO testTable
   		  SELECT '1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,'
   		  UNION ALL
   		  SELECT 'sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,'
   		  UNION ALL
   		  SELECT 'ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,'
   		  SELECT * FROM testTable a CROSS APPLY SPLIT(a.data,',')
   		  DROP TABLE testtable

然后我把这个函数改成多语表值函数,即返回表变量,却解决了上面的两个问题,既可以任意过滤结果,也可以"比较高效"地Apply.
我想知道为什么会出现这样的情况,内联表值函数到底有何特殊之处?它的处理机制是怎样的?

[快溜]

cross apply跟cross join是差不多的，唯一的区别在于，corss apply连接表值函数可以传入前表的字段作为参数，cross join只能穿固定的数值，之前的版本一直缺少关联表值函数传表字段的功能，所以微软在08的版本里加了这个新东西。

[三断笛]

[Quote=引用 1 楼 ssp2009 的回复:]

cross apply跟cross join是差不多的，唯一的区别在于，corss apply连接表值函数可以传入前表的字段作为参数，cross join只能穿固定的数值，之前的版本一直缺少关联表值函数传表字段的功能，所以微软在08的版本里加了这个新东西。
[/Quote]
嗯,谢谢,我还想知道为什么这个内联表值函数无法进行过滤,而且为什么做apply运算时这么慢.

[怪众生太美丽]

apply 操作符应该是2005新增的DML增强功能,与JOIN有相似的功能,apply操作符对于查询操作的外部表
（左输入）返回的每个行都会对右输入进行运算，这个右输入一般是表值函数，并且函数会取左边输入的
列值作为函数的参数；
APPLY有两种形式：cross apply 以及outer apply 两者的区别在于后者返回是否返回空结果集行,
outer apply都返回。
这个只是在学习的时候用到过，在性能调优问题上基本没测试过..就不懂了。只是了解了基本的使用
方法.

[叶子]

SELECT * FROM testTable a CROSS APPLY newSplit(a.data,',')
/*
DATA                                                         list     
------------------------------------------------------------ ---------
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    1
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    23
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    43
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    4
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    2
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    42
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    23
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    4
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    5
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    3
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    4
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    2
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    2
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    25
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    7
1,23,43,4,2,,42,,23,4,5,3,4,2,2,25,7,457,                    457
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    sd
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    sgas
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    g
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    fdg
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    f
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    as
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    2
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    e
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    23
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    er
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    gf
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    d
sd,sgas,g,fdg,,f,as,,2,e,23,er,gf,d,sdf2,                    sdf2
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       ad
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       ads
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       s
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       g
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       fg
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       we
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       wer
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       g
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       fg
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       a
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       d
ad,ads,,s,g,fg,,,we,wer,,g,fg,a,d,,aa,                       aa

(41 row(s) affected)
*/

我测试的速度还是很快的，不知道楼主的非常慢是什么原因！

[三断笛]

[Quote=引用 4 楼 maco_wang 的回复:]

SQL code

SELECT * FROM testTable a CROSS APPLY newSplit(a.data,',')
/*
DATA list
-------------------------------------------------------……
[/Quote]
那可能是我机器比较烂了...我一直测试都慢得很,不知道啥原因.
那关于这个函数加where条件总是报错,不知道您有什么高见?

[怪众生太美丽]

右输入为派生表时可以用where条件：

SELECT * FROM @A A OUTER APPLY
              (SELECT * FROM @B
               WHERE ID=A.ID)B
          --等价于
SELECT * FROM @A A LEFT JOIN @B B
ON A.ID=B.ID

[Quote=引用 5 楼 xxyj6450 的回复:]
引用 4 楼 maco_wang 的回复:

SQL code

SELECT * FROM testTable a CROSS APPLY newSplit(a.data,',')
/*
DATA list
-------------------------------------------------------……

那可能是我机器比较烂了...我一直测试都慢得很,不知道……
[/Quote]

[叶子]

[Quote=引用 5 楼 xxyj6450 的回复:]
引用 4 楼 maco_wang 的回复:

SQL code

SELECT * FROM testTable a CROSS APPLY newSplit(a.data,',')
/*
DATA list
-------------------------------------------------------……

那可能是我机器比较烂了...我一直测试都慢得很,不知道……
[/Quote]
我这不报错。

[叶子]

把你创建函数的整个语句发出来我看看！

[叶子]

我估计是你的变量长度定义的不够！

[NBDBA]

标题党？

[mengmou]

这种拆分方法在2005之后已经过时了，用SQL Server的xml函数拆分字符串效率更高。

[三断笛]

[Quote=引用 11 楼 mengmou 的回复:]

这种拆分方法在2005之后已经过时了，用SQL Server的xml函数拆分字符串效率更高。
[/Quote]
嗯,那为什么我这个函数加where条件就不行了呢?我实在想不明白这点

[兔子侠客]

MARK!!

[西蒙君霸占阿花]

学习了/.