提高MSSQL速度的实例有关问题。高手帮分析下。

提高MSSQL速度的实例问题。高手帮分析下。。
有两个表:

tblist
---------------------------------------------
id sname keylist
1 ABC 1,2,3,4
2 BCD 3,4,5
3 EEE 6,7,8,9,10,11,12,13
4 FFF 1,5,7
5 XXX 6,8,9
6 PPP 7,9,10,11,12,15
---------------------------------------------

keywords_tb表
--------------------------------------------
id keyname
1 k1
2 k2
3 k3
4 k4
5 k5
6 k6
7 k7
8 k8
9 k9
10 k10
11 k11
12 k12
13 k13
14 k14
15 k15
16 k16
...................
----------------------------------------------

现在要用SQL语句得到这样的结果.
col0 col1 col2
-----------------------------
1 ABC k1,k2,k3,k4
2 BCD k3,k4,k5

----------------------------
我现在采用的方法如下：

select sname ,dbo.jfun(keylist) as keynamelist from tblist

其中jfun是一个函数代码如下:



CREATE FUNCTION jfun
( 
@Idlist varchar(500) 
) 
RETURNS varchar(1024) 
AS 
BEGIN 
declare @Str varchar(1024) 
set @Str = '' 
select @Str = @Str + [KeyName]+',' from keywords_tb 
where ','+@Idlist+',' like '%,'+cast(id as varchar)+',%'
return @Str 
END

=============================================================

结果是正确显示了。
不过速度不够理想。

当显示100条记录的时候，要用2，3秒时间。
有没有什么办法改进一行。我想得到1秒以内的显示速度。

[解决办法]
这样的拆分字符串效率都不会高

2005+可以试试CTE
[解决办法]

SQL code

select sname,   keylist=stuff((select ','+keyname from keywords_tb    where charindex(','+rtrim(id)+',',','+a.keylist+',')>0 for xml path('')),1,1,'')from tblist a
[解决办法]
你的表结构没什么好的效率
[解决办法]
try this,
SQL code
create table tblist(id int, sname varchar(5), keylist varchar(20))insert into tblistselect 1, 'ABC', '1,2,3,4' union allselect 2, 'BCD', '3,4,5' union allselect 3, 'EEE', '6,7,8,9,10,11,12,13' union allselect 4, 'FFF', '1,5,7' union allselect 5, 'XXX', '6,8,9' union allselect 6, 'PPP', '7,9,10,11,12,15'create table keywords_tb(id int, keyname varchar(5))insert into keywords_tbselect 1, 'k1' union allselect 2, 'k2' union allselect 3, 'k3' union allselect 4, 'k4' union allselect 5, 'k5' union allselect 6, 'k6' union allselect 7, 'k7' union allselect 8, 'k8' union allselect 9, 'k9' union allselect 10, 'k10' union allselect 11, 'k11' union allselect 12, 'k12' union allselect 13, 'k13' union allselect 14, 'k14' union allselect 15, 'k15' union allselect 16, 'k16'with t as(select c.id,d.keyname from(select a.id,substring(a.keylist,b.number,charindex(',',a.keylist+',',b.number)-b.number) akfrom tblist ainner join master.dbo.spt_values bon b.[type]='P' and substring(','+a.keylist,b.number,1)=',') cinner join keywords_tb d on c.ak=d.id),t3 as(select t1.id,cast((select t2.keyname+',' from t t2 where t2.id=t1.id for xml path('')) as varchar) ak2from t t1group by t1.id)select a.id 'col0',b.sname 'col1',left(a.ak2,len(a.ak2)-1) 'col2'from t3 ainner join tblist bon a.id=b.idorder by a.idcol0        col1  col2----------- ----- ------------------------------1           ABC   k1,k2,k3,k42           BCD   k3,k4,k53           EEE   k6,k7,k8,k9,k10,k11,k12,k134           FFF   k1,k5,k75           XXX   k6,k8,k96           PPP   k7,k9,k10,k11,k12,k15(6 row(s) affected)