分页存储过程由于数据库数据量大，需要10秒才可以加载完成，每次都是这样，有什么好办法可以优化分页存储过程吗,可以说出这里面的语句不效率的地方怎么修改才好指个方向

[qq_41711958]

这里是数据库分页存储过程:
ALTER procedure [dbo].[SP_Get_ALLCarSequenceInfo]
(
@Production_Num nvarchar(100),
@Shift_No nvarchar(20),
@ReceivedDateBg nvarchar(20),
@ReceivedDateEd nvarchar(20),
@VIN nvarchar(20),
@Type nvarchar(1),
@PageIndex int,
@PageCount int
)
as
declare @sql2 int;
declare @sql nvarchar(2000);
declare @count nvarchar(1000);
declare @where nvarchar(1000);
--Select * into #Temp from (
--select distinct(A.ID), A.Production_Num, B.Shift_No, A.Car_Model, Seq_Code, Production_Index,B.VIN,B.Create_Time from dbo.TT_BAS_Car_Sequence A
--left join (select Product_Num, VIN,max(Create_Time)Create_Time,Shift_No from TT_SPS_PartsOfCar where Product_Num in (select Production_Num from TT_BAS_Car_Sequence) group by Product_Num,VIN,Shift_No ) B
--on A.Production_Num=B.Product_Num
--)as TP --,Production_Num,Shift_No
set @sql2=@PageCount*@PageIndex;
select top (@sql2) A.ID, A.Production_Num,Seq_Code,B.Shift_No,B.VIN,B.Create_Time into #Temp from dbo.TT_BAS_Car_Sequence A
left join (select Product_Num, VIN,max(Create_Time)Create_Time,Shift_No from TT_SPS_PartsOfCar group by Product_Num,VIN,Shift_No ) B
on A.Production_Num=B.Product_Num

CREATE INDEX Ix_temp ON #Temp(ID) --为临时表创建非聚合索引


if(@Type='1')
begin
--set @count='select MAX(Production_Index) as Production_Index,Max(Seq_Code) as Seq_Code,
--Production_Num,Shift_No,VIN,MAX(Create_Time) as Create_Time from #Temp group by Production_Num,Shift_No,VIN ,Production_Index having Production_num in(select ms_child from dbo.TT_SPS_Message_Received where 1=1';
set @count='select MAX(ID) as ID,Max(Seq_Code) as Seq_Code,
Production_Num,Shift_No,VIN,MAX(Create_Time) as Create_Time from #Temp group by Production_Num,Shift_No,VIN ,ID having Production_num in(select ms_child from dbo.TT_SPS_Message_Received where 1=1';

--set @sql='select top '+CONVERT(nvarchar,@PageCount) +'* from ( select row_number() over (order by Production_Index desc)as RowNumber,MAX(Production_Index) as Production_Index,Max(Seq_Code) as Seq_Code,
--Production_Num,Shift_No,VIN,MAX(Create_Time) as Create_Time from #Temp A
--group by Production_Num,Shift_No,VIN ,Production_Index having Production_num in(select ms_child from dbo.TT_SPS_Message_Received where 1=1';
set @sql='select top '+CONVERT(nvarchar,@PageCount) +'* from ( select row_number() over (order by ID desc)as RowNumber,MAX(ID) as ID,Max(Seq_Code) as Seq_Code,
Production_Num,Shift_No,VIN,MAX(Create_Time) as Create_Time from #Temp A
group by Production_Num,Shift_No,VIN ,ID having Production_num in(select ms_child from dbo.TT_SPS_Message_Received where 1=1';
end
else
begin
--set @count='select count(ID) as Counts from #Temp where Production_num in(select ms_child from dbo.TT_SPS_Message_Received where 1=1';
set @count='select count(ID) as Counts from #Temp where Production_num in(select ms_child from dbo.TT_SPS_Message_Received where 1=1';
--set @sql='select top '+CONVERT(nvarchar,@PageCount) +'* from ( select row_number() over (order by Production_Index desc)as RowNumber,A.* from #Temp A where Production_num in(select ms_child from dbo.TT_SPS_Message_Received where 1=1';
set @sql='select top '+CONVERT(nvarchar,@PageCount) +'* from ( select row_number() over (order by ID desc)as RowNumber,A.* from #Temp A where Production_num in(select ms_child from dbo.TT_SPS_Message_Received where 1=1';
end

set @where='';
if(@ReceivedDateBg!=''and @ReceivedDateEd!='')
set @where+='and Create_Time between'''+@ReceivedDateBg+'''and '''+@ReceivedDateEd+''''
if(@ReceivedDateBg!=''and @ReceivedDateEd='')
set @where+='and Create_Time between '''+@ReceivedDateBg+''' and '''+substring(@ReceivedDateBg,0,charindex(':',@ReceivedDateBg)-2)+' 23:59:59'''
if(1=1)
begin
set @where+=')'
end
if(@Production_Num!='')
set @where+='and Production_Num='''+@Production_Num+''''
if(@Shift_No!='')
set @where+='and Shift_No = '''+@Shift_No+''''
if(@VIN!='')
set @where+='and VIN = '''+@VIN+''''

set @sql+=@where+')T where RowNumber>'+convert(nvarchar,@PageCount)+'*('+convert(nvarchar,@PageIndex)+'-1) order by ID desc,Shift_No desc;'+'select count(1) as Counts from ('+@count+@where+') as CTB';
--print( @sql)
exec(@sql)

[吉普赛的歌]

不想看一大堆的 sql , 没意义
你直接把 print 之后的 慢 sql 贴出来