try this:
create table numbers(number int)
GO
declare @int int
set @int=1
while @int<8000
begin
insert into numbers(number) values(@int)
select @int=@int+1
end
GO
CREATE FUNCTION dbo.SplitString
(
@List TEXT,
@Delimiter CHAR(1)
)
RETURNS @ReturnTbl TABLE (OutParam VARCHAR(20))
WITH SCHEMABINDING
AS
--from
http://www.sqljunkies.com/WebLog/amachanic/articles/SplitString.aspx
BEGIN
DECLARE @LeftSplit VARCHAR(7998)
DECLARE @SplitStart INT SET @SplitStart = 0
DECLARE @SplitEnd INT
SET @SplitEnd = 7998
SELECT @SplitEnd = MAX(Number)
FROM dbo.Numbers
WHERE (SUBSTRING(@List, Number, 1) = @Delimiter
OR Number = DATALENGTH(@List) + 1)
AND Number BETWEEN @SplitStart AND @SplitEnd
WHILE @SplitStart < DATALENGTH(@List) - 1
BEGIN
SET @LeftSplit = @Delimiter + SUBSTRING(@List, @SplitStart, @SplitEnd -
@SplitStart) + @Delimiter
INSERT @ReturnTbl (OutParam)
SELECT LTRIM(RTRIM(SUBSTRING(@LeftSplit, Number + 1,
CHARINDEX(@Delimiter, @LeftSplit, Number + 1) - Number
- 1)))
AS Value
FROM dbo.Numbers
WHERE Number <= LEN(@LeftSplit) - 1
AND SUBSTRING(@LeftSplit, Number, 1) = @Delimiter
AND SUBSTRING(@LeftSplit, Number + 1,
CHARINDEX(@Delimiter, @LeftSplit, Number + 1) - Number
- 1) <> ''
SET @SplitStart = @SplitEnd + 1
SET @SplitEnd = @SplitEnd + 7998
SELECT @SplitEnd = MAX(Number) + @SplitStart
FROM dbo.Numbers
WHERE (SUBSTRING(@List, Number + @SplitStart, 1) = @Delimiter
OR Number+@SplitStart = DATALENGTH(@List) + 1)
AND Number BETWEEN 1 AND @SplitEnd - @SplitStart
END
RETURN
END
GO
Create table Noise(noiseword varchar(20))
GO
insert into Noise(noiseword) values('about')
insert into Noise(noiseword) values('after')
insert into Noise(noiseword) values('all')
insert into Noise(noiseword) values('also')
insert into Noise(noiseword) values('an')
insert into Noise(noiseword) values('and')
insert into Noise(noiseword) values('another')
insert into Noise(noiseword) values('any')
insert into Noise(noiseword) values('are')
insert into Noise(noiseword) values('as')
insert into Noise(noiseword) values('at')
insert into Noise(noiseword) values('be')
insert into Noise(noiseword) values('because')
insert into Noise(noiseword) values('been')
insert into Noise(noiseword) values('before')
insert into Noise(noiseword) values('being')
insert into Noise(noiseword) values('between')
insert into Noise(noiseword) values('both')
insert into Noise(noiseword) values('but')
insert into Noise(noiseword) values('by')
insert into Noise(noiseword) values('came')
insert into Noise(noiseword) values('can')
insert into Noise(noiseword) values('come')
insert into Noise(noiseword) values('could')
insert into Noise(noiseword) values('did')
insert into Noise(noiseword) values('do')
insert into Noise(noiseword) values('does')
insert into Noise(noiseword) values('each')
insert into Noise(noiseword) values('else')
insert into Noise(noiseword) values('for')
insert into Noise(noiseword) values('from')
insert into Noise(noiseword) values('get')
insert into Noise(noiseword) values('got')
insert into Noise(noiseword) values('has')
insert into Noise(noiseword) values('had')
insert into Noise(noiseword) values('he')
insert into Noise(noiseword) values('have')
insert into Noise(noiseword) values('her')
insert into Noise(noiseword) values('here')
insert into Noise(noiseword) values('him')
insert into Noise(noiseword) values('himself')
insert into Noise(noiseword) values('his')
insert into Noise(noiseword) values('how')
insert into Noise(noiseword) values('if')
insert into Noise(noiseword) values('in')
insert into Noise(noiseword) values('into')
insert into Noise(noiseword) values('is')
insert into Noise(noiseword) values('it')
insert into Noise(noiseword) values('its')
insert into Noise(noiseword) values('just')
insert into Noise(noiseword) values('like')
insert into Noise(noiseword) values('make')
insert into Noise(noiseword) values('many')
insert into Noise(noiseword) values('me')
insert into Noise(noiseword) values('might')
insert into Noise(noiseword) values('more')
insert into Noise(noiseword) values('most')
insert into Noise(noiseword) values('much')
insert into Noise(noiseword) values('must')
insert into Noise(noiseword) values('my')
insert into Noise(noiseword) values('never')
insert into Noise(noiseword) values('now')
insert into Noise(noiseword) values('of')
insert into Noise(noiseword) values('on')
insert into Noise(noiseword) values('only')
insert into Noise(noiseword) values('or')
insert into Noise(noiseword) values('other')
insert into Noise(noiseword) values('our')
insert into Noise(noiseword) values('out')
insert into Noise(noiseword) values('over')
insert into Noise(noiseword) values('re')
insert into Noise(noiseword) values('said')
insert into Noise(noiseword) values('same')
insert into Noise(noiseword) values('see')
insert into Noise(noiseword) values('should')
insert into Noise(noiseword) values('since')
insert into Noise(noiseword) values('so')
insert into Noise(noiseword) values('some')
insert into Noise(noiseword) values('still')
insert into Noise(noiseword) values('such')
insert into Noise(noiseword) values('take')
insert into Noise(noiseword) values('than')
insert into Noise(noiseword) values('that')
insert into Noise(noiseword) values('the')
insert into Noise(noiseword) values('their')
insert into Noise(noiseword) values('them')
insert into Noise(noiseword) values('then')
insert into Noise(noiseword) values('there')
insert into Noise(noiseword) values('these')
insert into Noise(noiseword) values('they')
insert into Noise(noiseword) values('this')
insert into Noise(noiseword) values('those')
insert into Noise(noiseword) values('through')
insert into Noise(noiseword) values('to')
insert into Noise(noiseword) values('too')
insert into Noise(noiseword) values('under')
insert into Noise(noiseword) values('up')
insert into Noise(noiseword) values('use')
insert into Noise(noiseword) values('very')
insert into Noise(noiseword) values('want')
insert into Noise(noiseword) values('was')
insert into Noise(noiseword) values('way')
insert into Noise(noiseword) values('we')
insert into Noise(noiseword) values('well')
insert into Noise(noiseword) values('were')
insert into Noise(noiseword) values('what')
insert into Noise(noiseword) values('when')
insert into Noise(noiseword) values('where')
insert into Noise(noiseword) values('which')
insert into Noise(noiseword) values('while')
insert into Noise(noiseword) values('who')
insert into Noise(noiseword) values('will')
insert into Noise(noiseword) values('with')
insert into Noise(noiseword) values('would')
insert into Noise(noiseword) values('you')
insert into Noise(noiseword) values('your')
insert into Noise(noiseword) values('a')
insert into Noise(noiseword) values('b')
insert into Noise(noiseword) values('c')
insert into Noise(noiseword) values('d')
insert into Noise(noiseword) values('e')
insert into Noise(noiseword) values('f')
insert into Noise(noiseword) values('g')
insert into Noise(noiseword) values('h')
insert into Noise(noiseword) values('i')
insert into Noise(noiseword) values('j')
insert into Noise(noiseword) values('k')
insert into Noise(noiseword) values('l')
insert into Noise(noiseword) values('m')
insert into Noise(noiseword) values('n')
insert into Noise(noiseword) values('o')
insert into Noise(noiseword) values('p')
insert into Noise(noiseword) values('q')
insert into Noise(noiseword) values('r')
insert into Noise(noiseword) values('s')
insert into Noise(noiseword) values('t')
insert into Noise(noiseword) values('u')
insert into Noise(noiseword) values('v')
insert into Noise(noiseword) values('w')
insert into Noise(noiseword) values('x')
insert into Noise(noiseword) values('y')
insert into Noise(noiseword) values('z')
insert into Noise(noiseword) values('$')
insert into Noise(noiseword) values('1')
insert into Noise(noiseword) values('2')
insert into Noise(noiseword) values('3')
insert into Noise(noiseword) values('4')
insert into Noise(noiseword) values('5')
insert into Noise(noiseword) values('6')
insert into Noise(noiseword) values('7')
insert into Noise(noiseword) values('8')
insert into Noise(noiseword) values('9')
insert into Noise(noiseword) values('0')
insert into Noise(noiseword) values('_')
GO
declare @searchParagraph varchar(8000)
set @searchParagraph='Sample search paragraph:
Sources: Crennel needs miracle finish to keep job
Browns head coach Romeo Crennel stated emphatically this week that
Brady Quinn will be Cleveland''s starting quarterback in 2009. Crennel
might not be Quinn''s head coach next season, however.'
declare @counter int
set @counter=0
while @counter<47
begin
select @searchParagraph =REPLACE(@searchParagraph,char(@counter),' ')
select @counter=@counter+1
end
set @counter=58
while @counter<65
begin
select @searchParagraph =REPLACE(@searchParagraph,char(@counter),' ')
select @counter=@counter+1
end
set @counter=91
while @counter<96
begin
select @searchParagraph =REPLACE(@searchParagraph,char(@counter),' ')
select @counter=@counter+1
end
set @counter=123
while @counter<256
begin
select @searchParagraph =REPLACE(@searchParagraph,char(@counter),' ')
select @counter=@counter+1
end
declare @table table(word varchar(50))
insert into @table
select distinct outparam from dbo.SplitString(@searchParagraph,' ') where
outparam not in (select noiseword from Noise)
set @searchParagraph=CHAR(39)
select @searchParagraph=@searchParagraph+word +char(39)+' OR ' + char(39)
from @table
select @searchParagraph=reverse(substring(reverse(@searchParagraph),6,8000))
print @searchParagraph
declare @table1 table(id int, primary key (id))
insert into @table1
select [key] from
COntainstable(tblDirectory2,(Title,Description),@searchparagraph,LANGUAGE
'English' )
select d.FirstTopic as Topic, COUNT(D.ID) count from tblDirectory2 d
join @table1 r
on d.Id=r.id
group by d.FirstTopic
order by count desc
do set statistics io and post back here.
"OGG" wrote:
> Sample search paragraph:
> "Sources: Crennel needs miracle finish to keep job
> Browns head coach Romeo Crennel stated emphatically this week that
> Brady Quinn will be Cleveland's starting quarterback in 2009. Crennel
> might not be Quinn's head coach next season, however."
>
> Here is the message after executing with statistic io:
> Informational: The full-text search condition contained noise word(s).
> Table 'Worktable'. Scan count 1, logical reads 388252, physical reads
> 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
> read-ahead reads 0.
>
> (189519 row(s) affected)
>
> (1 row(s) affected)
>
> (16 row(s) affected)
> Table 'tblDirectory2'. Scan count 0, logical reads 568557, physical
> reads 34, read-ahead reads 0, lob logical reads 0, lob physical reads
> 0, lob read-ahead reads 0.
> Table '#279A9293'. Scan count 1, logical reads 306, physical reads 0,
> read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
> read-ahead reads 0.
>
> (1 row(s) affected)
> >> Stay informed about: SQL 2008 integrated Full-Text search - VERY slow queries! 
FAQ
Search
Profile
Private Messages
Log in
