如果数据库有100W条数据同时查询出来，以下储存过程很慢，请大神们帮忙看看，如何优化以下这段代码。
这段代码的功能是：分页带查询
CREATE PROC [dbo].[jxc_djb_lszb_PRO]
@xszts VARCHAR(20),
@pagecount INT,
@pageindex INT,
@mhbl VARCHAR(20),
@tbname VARCHAR(20),
@sh VARCHAR(20),
@fd VARCHAR(50),
@bm VARCHAR(50),
@fl VARCHAR(20),
@kssj DATETIME2,
@jssj DATETIME2,
@tablezd VARCHAR(20),
@mhgjz VARCHAR(20),
@sortzm VARCHAR(20),
@tjbl VARCHAR(20),
@zcount INT OUT,
@outzpagecount int out
AS
DECLARE @ccgcstr1 NVARCHAR(4000)/*查询正常总数量*/
DECLARE @ccgcstr2 NVARCHAR(4000)/*分页显示和单页导出到EXCEL*/
DECLARE @ccgcstr3 NVARCHAR(4000)/*全部导出到EXCL*/
SET @ccgcstr1='SELECT @zcount=COUNT(*) FROM '+@tbname+' WHERE (ysbm like ''%'+@fd+'%'''+' and ysbm like ''%'+@bm+'%'''+') AND Assort LIKE ''%'+@fl+'%'''+' AND Provider LIKE ''%'+@sh+'%'''+' AND XSZT='+CHAR(39)+@xszts+CHAR(39)+' AND (GoodsName LIKE ''%'+@mhbl+'%'''+' OR BPMF LIKE ''%'+@mhbl+'%'''+') AND '+@tablezd+' LIKE ''%'+@mhgjz+'%'''+' AND czsj BETWEEN '+CHAR(39)+CONVERT(VARCHAR,@kssj)+CHAR(39)+' and '+CHAR(39)+CONVERT(VARCHAR,@jssj)+CHAR(39)
exec sp_executesql @ccgcstr1,N'@zcount int out',@zcount OUT/*总行数*/
SET @zcount=@zcount
SET @outzpagecount=CEILING(@zcount*1.0/@pagecount)/*总页数*/
SET @ccgcstr2='SELECT TOP ('+CONVERT(VARCHAR,@pagecount)+') * from '+@tbname+' WHERE (ysbm like ''%'+@fd+'%'''+' and ysbm like ''%'+@bm+'%'''+') AND Assort LIKE ''%'+@fl+'%'''+' AND Provider LIKE ''%'+@sh+'%'''+' AND XSZT='+CHAR(39)+@xszts+CHAR(39)+' AND (GoodsName LIKE ''%'+@mhbl+'%'''+' OR BPMF LIKE ''%'+@mhbl+'%'''+') AND '+@tablezd+' LIKE ''%'+@mhgjz+'%'''+' AND czsj BETWEEN '+CHAR(39)+CONVERT(VARCHAR,@kssj)+CHAR(39)+' and '+CHAR(39)+CONVERT(VARCHAR,@jssj)+CHAR(39)+' AND ID NOT IN (SELECT TOP ('+CONVERT(VARCHAR,@pageindex*@pagecount)+') ID FROM '+@tbname+' WHERE (ysbm like ''%'+@fd+'%'''+' and ysbm like ''%'+@bm+'%'''+') AND Assort LIKE ''%'+@fl+'%'''+' AND Provider LIKE ''%'+@sh+'%'''+' AND XSZT='+CHAR(39)+@xszts+CHAR(39)+' AND (GoodsName LIKE ''%'+@mhbl+'%'''+' OR BPMF LIKE ''%'+@mhbl+'%'''+') AND '+@tablezd+' LIKE ''%'+@mhgjz+'%'''+' AND czsj BETWEEN '+CHAR(39)+CONVERT(VARCHAR,@kssj)+CHAR(39)+' and '+CHAR(39)+CONVERT(VARCHAR,@jssj)+CHAR(39)+' order by '+@sortzm+ ') order by '+ @sortzm
SET @ccgcstr3='SELECT * from '+@tbname+' WHERE (ysbm like ''%'+@fd+'%'''+' and ysbm like ''%'+@bm+'%'''+') AND Assort LIKE ''%'+@fl+'%'''+' AND Provider LIKE ''%'+@sh+'%'''+' AND XSZT='+CHAR(39)+@xszts+CHAR(39)+' AND (GoodsName LIKE ''%'+@mhbl+'%'''+' OR BPMF LIKE ''%'+@mhbl+'%'''+') AND '+@tablezd+' LIKE ''%'+@mhgjz+'%'''+' AND czsj BETWEEN '+CHAR(39)+CONVERT(VARCHAR,@kssj)+CHAR(39)+' and '+CHAR(39)+CONVERT(VARCHAR,@jssj)+CHAR(39)
IF @tjbl='no'
BEGIN
exec sp_executesql @ccgcstr2
END
ELSE IF @tjbl='dcdqy'
BEGIN
exec sp_executesql @ccgcstr2
END
ELSE IF @tjbl='dcqb'
BEGIN
exec sp_executesql @ccgcstr3
END
------解决思路----------------------
你这里等于执行了3个类似的select错作。
你可以在第一次就选出最后需要显示的所有信息放入临时表（建议给临时表添加index），然后根据表变量中的数据，赋值 @zcount， @outzpagecount，最后选取所需要的数据。
应该可以减少2/3的执行时间
------解决思路----------------------
100W的数据，你用的动态脚本，里面使用了大量的like '%%'索引完全无效的，慢是肯定的，不用想了。你传进去的参数值不能先过滤下？比方说，@bm这个参数，有没有表格单独存放编码数据的，先从这个表中过滤数据写进临时表，然后和你传进去的表关联，这样会快很多。2楼纯属误人子弟