帮忙优化个sql语句

[Iovswety]

场景大概如下，有BookInfo以及QueryPlan两张表，分别是表示书籍、我的定制。
我的定制的意思是，用户可以设置自己定制的方案，之后一进系统就显示该方案对应的书籍。

表结构大概如下：

BookInfo:BookID,BookName,BookTypeID(所属分类的ID),Author(作者),press(出版社)...
QueryPlan:UserID,Flag(标志该方案是否为默认方案，用户可以有多个方案),BookTypeID(数据类型大概是",a,b,fas,fre,"表示用户该定制方案是这四种书籍的分类),Author(作者),press(出版社)...大概就这么些字段。

我自己写的语句如下：

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)

由于BookInfo表数据量是一百万以上的，执行以上语句差不多是30秒。用户接受不了。请各位高手们帮帮忙，怎么优化这个语句，也可以通过索引或者其他的办法，只要行得通就可以了。但是修改表结构的办法就不用了，项目是二期的，没得对表结构做修改。。

[cd731107]

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
exists (select 1 from dbo.QueryPlan where UserID = 'sunyusheng' and Flag = 'true' and charindex(b.author,author)>0)
or
exists (select 1 from dbo.QueryPlan where UserID = 'sunyusheng' and Flag = 'true' and charindex(b.press,press)>0)

[cd731107]

--或
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)>0 or charindex(b.press,press)>0))

[bancxc]

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)）

[Iovswety]

刚试过了，还是需要30秒，比我写的方法快2秒左右。。

[--小F--]

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)）

[--小F--]

[Quote=引用 4 楼 iovswety 的回复:]
刚试过了，还是需要30秒，比我写的方法快2秒左右。。
[/Quote]
字段设计这样 没多大的优化空间啊

[Iovswety]

那如果可以改表结构的话，改如何设计表以满足功能呢??

[Andy-W]

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

--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
				)

--

[fanzhouqi]

like 都比 charindex 快（因为like ‘xxx%’ 能应用到索引）
如果lz 的应用层 是自己开发的，那么，就根据业务，优化一下。

[fanzhouqi]

对于无法使用 like 跑索引的，用全文索引试试看

[wujianfeng32]

帮顶

[lilyc_sea]

帮忙顶一下

[ORARichard]

在楼主的查询中，没必要用charindex吧。比如查作者“张三”的书，有必要把叫“张三四”的书也查出来吗？去掉那些charindexes查询也就成了
select ... from A inner join B on a.x=b.x or a.y=b.y or a.z=b.z
where b.v=... and b.w=....

即便一定要charindex也可以用','+a+',' like '%,'+b+',%' 来替换。

[ORARichard]

另外贴出你的执行计划，别人才能帮你优化。

[cs_lb]

在以下字段建立索引
Booktypeid

author

press

UserID

Flag

[北漂小浪仔]

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

[l4kangaxx]

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

[勿勿]

估计有点难