这段存储过程需要怎么优化才行，现在的执行速度有点慢，在数据库都已经需要1秒了
现在的这个存储过程有点慢，在数据库的查询时间就已经在1秒左右
前台我需要逻辑判断，所以这是不行的！看看能够怎么优化吧！
- SQL code
/****** Object: StoredProcedure [dbo].[Query_SerchResultList] Script Date: 03/31/2012 15:22:52 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Query_SerchResultList]( @type INT, --1微博/2用户/3微群 @serchtag VARCHAR(20), --搜索关键词 @pageindex INT, --页码 @pagesize INT, --数据大小 @isCount INT --是否需要得到行数)AS DECLARE @sqlstr NVARCHAR(4000) DECLARE @taghave INT IF @isCount = 0 BEGIN --搜索微博 IF @type = 1 BEGIN SET @sqlstr = 'SELECT * FROM ( SELECT mb.ApplicationID, mb.BlogContent, mb.BlogUntreatedContent, mb.BlogSender, mb.BlogForward, mb.BlogFrom, mb.IsFirstPub, mb.BlogTime, mb.IsDeleted, mb.CommentCount, mb.ForwardCount, mb.OriginalBlogID, gi.GName, gi.GId, ROW_NUMBER() OVER(ORDER BY BlogTime DESC) AS Num FROM MicroBlog mb LEFT OUTER JOIN GroupInfo gi ON gi.GId = mb.GroupID WHERE mb.IsDeleted = 0 AND mb.BlogContent LIKE ''' + '%' + @serchtag + '%' + ''' )t1 WHERE t1.Num BETWEEN (' + CAST(@PageSize AS VARCHAR) + ' * (' + CAST(@PageIndex AS VARCHAR) + ' -1)) AND (' + CAST(@PageSize AS VARCHAR) + ' * ' + CAST(@PageIndex AS VARCHAR) + ') ORDER BY t1.BlogTime DESC'; END --搜索用户 IF @type = 2 BEGIN SET @sqlstr = 'SELECT TOP ' + CAST(@pagesize AS VARCHAR) + ' a.UserID,a.NickName,a.MyDiscription,a.SelfPicture,a.ConcernedCount,a.ConcerningCount,b.validated FROM UserInfo a, SysUser b Where a.NickName LIKE + ''' + '%' + @serchtag + '%' + ''' AND a.UserID = b.UserID AND a.UserID NOT IN ( SELECT TOP (' + CAST(@pagesize AS VARCHAR) + ' * (' + CAST(@pageindex AS VARCHAR) + ' - 1)) a.UserID FROM UserInfo a, SysUser b Where a.NickName LIKE + ''' + '%' + @serchtag + '%' + ''' AND a.UserID = b.UserID ORDER BY BlogCount desc ) ORDER BY BlogCount desc'; END --搜索微群 IF @type = 3 BEGIN SET @sqlstr = 'SELECT TOP ' + CAST(@pagesize AS VARCHAR) + ' a.GClassName,b.GId,b.GImage,b.GName,b.GSpeakSize,b.GUserSize,b.GDescription,b.GCreateDate,c.NickName,c.UserID FROM GroupClass a,GroupInfo b,UserInfo c WHERE a.GClassId = b.GGroupId AND b.GUserid = c.UserID AND b.GPrivate = 1 AND b.GName LIKE + ''' + '%' + @serchtag + '%' + ''' AND b.GId NOT IN ( SELECT TOP (' + CAST(@pagesize AS VARCHAR) + ' * (' + CAST(@pageindex AS VARCHAR) + ' - 1)) b.GId FROM GroupClass a,GroupInfo b,UserInfo c WHERE a.GClassId = b.GGroupId AND b.GUserid = c.UserID AND b.GPrivate = 1 AND b.GName LIKE + ''' + '%' + @serchtag + '%' + ''' ORDER BY b.GScore desc ) ORDER BY b.GScore desc'; END EXEC (@sqlstr) IF @serchtag <> '' BEGIN --是否已经存在此次搜索的keyword SELECT @taghave = COUNT(ID) FROM SerchRecord WHERE Keyword = CAST(@serchtag AS VARCHAR) --如果存在则做更新操作 IF @taghave > 0 BEGIN UPDATE SerchRecord SET SerchTime = GETDATE(), SerchSize = (SerchSize + 1) WHERE Keyword = @serchtag END--如果不存在则做新增的操作 ELSE BEGIN INSERT INTO SerchRecord ( ID, Keyword, SerchTime, SerchSize ) VALUES ( NEWID(), @serchtag, GETDATE(), 1 ) END END END IF @isCount = 1 BEGIN --搜索微博 IF @type = 1 BEGIN SET @sqlstr = 'SELECT COUNT(*) FROM MicroBlog mb WHERE mb.IsDeleted = 0 and mb.BlogContent LIKE ''' + '%' + @serchtag + '%' + ''''; END --搜索用户 IF @type = 2 BEGIN SET @sqlstr = ' SELECT COUNT(a.UserID) FROM UserInfo a, SysUser b WHERE a.NickName LIKE + ''' + '%' + @serchtag + '% ' + ''' AND a.UserID = b.UserID '; END --搜索微群 IF @type = 3 BEGIN SET @sqlstr = ' SELECT COUNT(b.GId) FROM GroupClass a, GroupInfo b, UserInfo c WHERE a.GClassId = b.GGroupId AND b.GUserid = c.UserID AND b.GPrivate = 1 AND b.GName LIKE + ''' + '%' + @serchtag + '% ' + ''''; END EXEC (@sqlstr) END
[解决办法]
好长好长。
[解决办法]
技术帖 不要灌水
坐等高人
[解决办法]
我不灌水
[解决办法]
[解决办法]
感觉没有可优化的地方了。看表的数据量了。
[解决办法]
分页啊
[解决办法]
你的动态语句执行部分 用 exec sp_executesql 这种方式执行试一下。这种缓存中保留执行计划，你那种是不保留的，每次都编译重新生成执行计划
[解决办法]
1秒还嫌慢?
[解决办法]
[解决办法]