以下是存储过程的部分代码:set @rstDocStorage=cursor scroll dynamic optimistic for select Title from tblDocStorage where RootID=@intRootID open @rstDocStorageset @rstKeywords=cursor scroll dynamic optimistic for select distinct Keyword from tblKeywords order by Keyword open @rstKeywords fetch first from @rstKeywords into @strKeywordwhile(@@FETCH_STATUS=0) begin fetch first from @rstDocStorage into @strTitle while(@@FETCH_STATUS=0) begin /*search,统计关键字出现次数*/ WHILE LEN(@strTitle) > 0 BEGIN SET @intIndex = CHARINDEX(@strKeyword,@strTitle) if (@intIndex!=0) begin set @intSearchStart=@intIndex+Len(@strKeyword) SET @strTemp = Right(@strTitle,@intSearchStart) set @intWordFrequenyNum=@intWordFrequenyNum+1 end else break END fetch next from @rstDocStorage into @strTitle end fetch next from @rstKeywords into @strKeyword end
john_1980(Sun) 存储过程里面有叫连接的吗?好像ado里面才有吧
nielisheng(阿土) 建个全文索引这个也试过,没有明显改善
set @rstKeywords=cursor scroll dynamic optimistic for select distinct Keyword from tblKeywords order by Keyword 改为 set @rstKeywords=cursor scroll dynamic optimistic for select distinct Keyword from tblKeywords,tblDocStorage where tblKeywords.Keyword like '%'+tblDocStorage.Title+'%' order by Keyword 这样不出现在tblDocStorage.Title的Keyword便不会被插入游标里,@rstKeywords的记录数就没有几千条了。
楼主把具体需求写出嘛!
select Title
from tblDocStorage where RootID=@intRootID
open @rstDocStorageset @rstKeywords=cursor scroll dynamic optimistic for
select distinct Keyword from tblKeywords
order by Keyword
open @rstKeywords
fetch first from @rstKeywords into @strKeywordwhile(@@FETCH_STATUS=0)
begin
fetch first from @rstDocStorage
into @strTitle
while(@@FETCH_STATUS=0)
begin
/*search,统计关键字出现次数*/
WHILE LEN(@strTitle) > 0
BEGIN
SET @intIndex = CHARINDEX(@strKeyword,@strTitle)
if (@intIndex!=0)
begin
set @intSearchStart=@intIndex+Len(@strKeyword)
SET @strTemp = Right(@strTitle,@intSearchStart)
set @intWordFrequenyNum=@intWordFrequenyNum+1
end
else
break
END
fetch next from @rstDocStorage into @strTitle
end
fetch next from @rstKeywords into @strKeyword
end
存储过程里面有叫连接的吗?好像ado里面才有吧
建个全文索引这个也试过,没有明显改善
select distinct Keyword from tblKeywords
order by Keyword
改为
set @rstKeywords=cursor scroll dynamic optimistic for
select distinct Keyword from tblKeywords,tblDocStorage where tblKeywords.Keyword like '%'+tblDocStorage.Title+'%'
order by Keyword
这样不出现在tblDocStorage.Title的Keyword便不会被插入游标里,@rstKeywords的记录数就没有几千条了。
tblKeywords.Keyword like '%'+tblDocStorage.Title+'%'
改为
tblDocStorage.Title like '%'+ tblKeywords.Keyword +'%'
可以吗?