存储过程执行越来越慢,删了重建立马变快,这是为神马?求指点
有一个简单的读取数据用的存储过程,刚建的时候查询很快,过段时间就变慢了,
然后删了重建,在程序中查询又变快了...很是纠结
删除重建的过程中没有重启程序,应该跟连接池无关,
然后相关表索引碎片经常清理,索引碎片应该也可以可以忽略...求高人指点,这可是我毕业的第一份工作啊
存储过程如下:
ALTER PROCEDURE [dbo].[XueyuanStudyState]
(
@icnum varchar(50),
@indexpage int,
@pagesize int,
@isall char(2),
@batchId varchar(50),
@semester varchar(50)
)
AS
BEGIN
declare @sql varchar(50)
declare @projectid varchar(50)
set @projectid=(select top 1 ProjectID from TrainingBach where BachID=@batchId)
select c.name as 栏目,p.name as 课程,
case result.tongguo when '0' then '否' when '1' then '是' else '否' end as 出勤,
case result.tongguo when '0' then '未通过' when '1' then '已通过' else '未通过' end as 课后练习,
p.credithour as 学时 into #tempTable
from course c
inner join course p on c.courseid=p.parerentid
inner join SemesterCouse sc on p.courseid=sc.coureid
inner join Semester s on sc.semesterid=s.semesterID
inner join TrainingBach t on s.BachID=t.BachID
left join
(select pe.icnum,id2,case when at.id2 is null then '0' else '1' end as tongguo
from vw_com_areaperson pe
left join
(select studentid,id2 from attendrecord where id1<>0 and id2<>0 and bachid=@batchId and semester=@semester) at
on pe.icnum=at.studentid where
pe.icnum=@icnum group by pe.icnum,id2) as result
on p.courseid=result.id2 where t.ProjectID=@projectid and sc.BachID=@batchId and sc.semesterid=@semester and s.BachID=@batchId and s.semesterid=@semester
set @sql='select * from #tempTable'
if @isall='1'
begin
exec(@sql)
end
else
begin
exec PrPage @sql,@indexpage,@pagesize
end
END