OK, we have halved our IO, this is a step if not a leap in the right
direction.
Let's try this now. This is your proc. Create it in the same database as you
have the noise table and the splitstring function. After you have created
the proc execute it and send me the execution plan. Run the test 1 time, and
then run it 10 times and time the results of the last 10 times.
Then send me results of an execution with set statistics io on.
create proc SearchMe(@searchParagraph varchar(8000))
as
set nocount off
select @searchParagraph=LOWER(@searchparagraph)
declare @len int
declare @counter int
declare @char char
set @counter=0
declare @newstr varchar(8000)
set @newstr=' '
set @len=LEN(@searchParagraph)
while @counter<@len
begin
select @char=''
select @char=case
when ascii(SUBSTRING(@searchparagraph,@counter,1)) between 97 and 122
then SUBSTRING(@searchparagraph,@counter,1)
else ' '
end
select @newstr=@newstr+@char
select @counter=@counter+1
end
declare @table table(word varchar(500))
insert into @table
select distinct outparam from dbo.SplitString(@newstr,' ') 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))
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
GO
"OGG" wrote in message
> Results:
> http://itye.no-ip.com/vlad/report1.zip
>
> (It took about 14 seconds to complete...) >> Stay informed about: SQL 2008 integrated Full-Text search - VERY slow queries! 
FAQ
Search
Profile
Private Messages
Log in
