这个存储过程要怎么优化
0
[已解决问题]
解决于 2010-05-14 14:36
CREATE PROCEDURE UP_news_GetList
@new_title nvarchar(200),
@new_type nvarchar(25)
AS
SELECT
*
FROM [LI_news]
where (new_title like '%' + @new_title + '%' or @new_title = '')
and new_type in ('0','1','2','3','4','5','6','7','8','9','10') order by id desc
这个存储过程要怎么优化
CREATE PROCEDURE UP_news_GetList@new_title nvarchar(200),@new_type nvarchar(25) AS
SELECT * FROM [LI_news] where (new_title like '%' + @new_title + '%' or @new_title = '') and new_type in ('0','1','2','3','4','5','6','7','8','9','10') order by id desc
这个存储过程要怎么优化
最佳答案
0
建议思路：
1、建立索引，考虑在new_type和Li_New表的addtime字段建立索引。
2、可以考虑,联合查询：
SELECT * FROM [LI_news] where (@new_title = '') and new_type in ('0','1','2','3','4','5','6','7','8','9','10')
union all
SELECT * FROM [LI_news] where (new_title like '%' + @new_title + '%') and new_type in ('0','1','2','3','4','5','6','7','8','9','10')
order by id desc
3、在不使用全文检索的情况下，应尽量避免在查询中使用ntext等大字段，应只列出列表所需的"瘦"字段
如select NID,New_Title,AddTime,curstate,new_Type等
其他回答(1)
0
在表上建索引试试看。另外，可以用 SQL Server 自带的 Database Engine Tuning Advisor 来看看该如何建索引。
