Speeding up searches - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Speeding up searches

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2002-08-09 : 05:11:20

I have a procedure to search the database. My problem is it gives timeout error sometimes. I think I need to speed it up but I don't know a better way to do it than I done it here.

I thought I better ask you, I'd be very glad if you could share your opinions with me.

Here's the procedure:


create procedure Search_Word
@word varchar(100),@fieldToSearch varchar(20)='ALL',@dateinterval varchar(20)='ALL',
@forumToSearch varchar(1000),@date varchar(100)
as
set nocount on

declare @sql nvarchar(3000)
declare @field varchar(3000)
declare @date2 varchar(3000)
declare @forum varchar(3000)

set @forum=' and K.K_Forum in'+@forumToSearch

if @fieldToSearch='ALL' 
	begin
		set @field=' ((M.M_Message like '''+@word+''') or (K.K_Topic like '''+@word+''') or (K.K_Message like '''+@word+''')) '
	end
if @fieldToSearch='topics' 
	begin
		set @field=' (K.K_Topic like '''+@word+''') '
	end
if @fieldToSearch='messages' 
	begin
		set @field=' ((M.M_Message like '''+@word+''') or (K.K_Message like '''+@word+''')) '
	end

if @dateinterval='ALL' 
	begin
		set @date2=''
	end
else 
	begin
		set @date2=' and (K.K_Date>'''+@date+''' or M.M_Date>'''+@date+''') '
	end

set @sql='
declare @tmpTable table (K_ID int,K_Topic varchar(200),K_Poster varchar(200),K_Date varchar(50),
							K_ReplyCount smallint,K_LastPoster varchar(200),K_LastReplyDate varchar(50),
							K_LastReplyTime varchar(50))

insert into @tmpTable
select	top 200 K.K_ID, K.K_Topic, (select U_Nickname from dbo.[MEMBERS] where U_ID=K.K_Poster) as K_LastPoster, K.K_Date,
		K.K_ReplyCount, (select U_Nickname from dbo.[MEMBERS] where U_ID=K.K_LastPoster) as K_LastPoster, 
		K.K_LastReplyDate,K.K_LastReplyTime
from	dbo.[TOPICS] as K left outer join dbo.[MESSAGES] as M 
on		K.K_ID = M.M_KonuID
where	'+@field+' 
		'+@date2+'
		'+@forum+' 
		and	
		(K.K_Statu=''1'')
order by K.K_LastReplyDate,K.K_LastReplyTime,K.K_Date

select distinct K_ID,K_Topic,K_Poster,K_Date,K_ReplyCount,K_LastPoster,K_LastReplyDate,K_LastReplyTime
from @tmpTable
order by K_LastReplyDate,K_LastReplyTime,K_Date desc'

exec dbo.sp_executesql @sql

go

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-09 : 08:39:10

First, I'd do away with the dynamic sql in favor of a CASE construct in your WHERE clause. Then I'd get rid of the temp table and just return the desire rowset. Finally, I would look at indexing.

Jay White
{0}

kensai
Posting Yak Master

172 Posts

Posted - 2002-08-13 : 16:14:53

I tried to using Case instead of dynamic sql. Because I'm not good at it I'm having a syntax error with it and I don't what I'm doing wrong. Could you please check it?

Here:


select	top 200 K.K_ID, K.K_Topic, (select U_Nickname from dbo.[MEMBERS] where U_ID=K.K_Poster) as K_LastPoster, K.K_Date,
		K.K_CevapSayisi, (select U_Nickname from dbo.[MEMBERS] where U_ID=K.K_LastPoster) as K_LastPoster, 
		K.K_LastReplyDate,K.K_LastReplyTime
from	dbo.[TOPICS] as K left outer join dbo.[MESSAGES] as M 
on	K.K_ID = M.M_KonuID
where	case @fieldToSearch
		when 'ALL' 		then  ((M.M_Message like @word) or (K.K_Topic like @word) or (K.K_Message like @word)) 
		when 'topics'		then  (K.K_Topic like @word) 
		when 'messages'		then  ((M.M_Message like @word) or (K.K_Message like @word)) 
	end
	case @dateinterval
		when not 'ALL' then  and (K.K_Date>@date or M.M_Time>@time) 
	end
	and K.K_Forum in @forumToSearch
	and (K.K_Statu='1')
order by K.K_LastReplyDate,K.K_LastReplyTime,K.K_Date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources