select top 5 BookID,BookName,b.Author,PressTime,Thumbnail from dbo.BookInfo b 
where 
charindex(b.Booktypeid,(select BookTypeID from dbo.QueryPlan where UserID = 'sunyusheng' and Flag = 'true')) >0 
or 
(charindex(author,(select author from dbo.QueryPlan where UserID = 'sunyusheng' and Flag = 'true')) > 0) 
or 
(charindex(press,(select press from dbo.QueryPlan where UserID = 'sunyusheng' and Flag = 'true')) > 0)

select
 top 5 BookID,BookName,b.Author,PressTime,Thumbnail from dbo.BookInfo b 
where 
 exists (select 1 from dbo.QueryPlan where UserID = 'sunyusheng' and Flag = 'true' and 
（charindex(b.Booktypeid,BookTypeID)>0 or charindex(b.author,author) or charindex(b.press,press)>0)）

------解决方案--------------------
主要的問題是charindex()在SQL Server 2000 & SQL Server 2005無法應用到索引，對應上百萬條記錄的BookInfo表，最好的解決方法就是，考慮能應用到合適的索引。可以參考下面方法：

SQL code


--1.在表BookInfo的Booktypeid author press字段創建合適索引


--2.轉換 Booktypeid author press成列表形式 ，以便能使用到索引
Declare @sql nvarchar(4000)

if object_id('Tempdb..#') Is Not Null Drop Table #
if object_id('Tempdb..#QueryPlan') Is Not Null Drop Table #QueryPlan
Create Table #QueryPlan(Detail nvarchar(50),DetailType smallint) /* 1.BookTypeID 2.author 3.press */
Create clustered  Index IX_#QueryPlan On #QueryPlan (DetailType Asc)

select author,BookTypeID,press Into # from dbo.QueryPlan where UserID = 'sunyusheng' and Flag = 'true'

If Exists(Select 1 from # Where BookTypeID>'')
Begin
    Set @sql='Select 1,'''+Replace((Select BookTypeID From #),',',''' union All Select 1,''')+''''
    insert into QueryPlan(DetailType,Detail) Exec (@sql)
End 

If Exists(Select 1 from # Where author>'')
Begin
    Set @sql='Select 2,'''+Replace((Select author From #),',',''' union All Select 2,''')+''''
    insert into QueryPlan(DetailType,Detail) Exec (@sql)
End 

If Exists(Select 1 from # Where press>'')
Begin
    Set @sql='Select 3,'''+Replace((Select press From #),',',''' union All Select 3,''')+''''
    insert into QueryPlan(DetailType,Detail) Exec (@sql)
End 

--3修改查詢語句,查看執行計劃，是否應用到索引，調整索引，這樣就能提高查詢效率

--3.a
select top 5 BookID,BookName,b.Author,PressTime,Thumbnail 
from dbo.BookInfo b 
Where Exists(Select 1 
                    From # 
                    Where (Detail=b.BookTypeID And DetailType=1) --BookTypeID
                        Or (Detail=b.author And DetailType=2) --author
                        Or (Detail=b.press And DetailType=3) --press
                )

--

------解决方案--------------------

由于在booktypeid,author,press字段大量用到字符以及字符串,所以加索引也未必有效,如果你这个系统不是24小时全时工作的话,我建议可以新建一表,将你自己写的那个sql语句做成存储过程,每天12点将你的存储过程的结果保存到表里,然后将"我的定制"这个默认功能从这张表里取,这样就算用户很多,但是因为不会出现charindex这样的运算,还是能快很多的,charindex真的伤不起啊