网站首页读取数据,使用一个存储过程,里面大概包含4个select的结果表[加起来不超过50条数据].返回到DataSet,使用DataSet绑定各个空间.里面只包含插入临时表的insert语句.问题在于,有时候执行的很快,1S不到,但有时候需要执行1分钟,同样的条件,同样的数据.这个会是什么原因呢?是程序问题还是病毒咧?存储过程代码如下:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER proc [dbo].[getdefinfo]
(
--获取首页的信息
@compid char(11),
@id char(11)
)
as
begin
--1.获取合同,回款的月度信息
declare @tb1 table([type] nvarchar(10),[sum] int default 0)
declare @sum1 int --合同额
select @sum1 = sum(pact_sum) from pact where pact_netuid = @id and year(pact_time) = year(getdate()) and month(pact_time) = month(getdate())
if(@sum1 is null)
set @sum1 = 0
insert into @tb1([type],[sum]) values('合同总额',@sum1)
------------------------------------------------------------------------------------------
declare @sum2 int --预计回款额
select @sum2 = sum(back_money) from pact inner join backmoney on back_pactID = pact_id where pact_netuid = @id and year(pact_time) = year(getdate()) and month(pact_time) = month(getdate())
if(@sum2 is null)
set @sum2 = 0
insert into @tb1([type],[sum]) values('预计回款额',@sum2)
-------------------------------------------------------------------------------------------
declare @sum3 int --已回款额
select @sum3 = sum(pact_backsum) from pact where pact_netuid = @id and year(pact_time) = year(getdate()) and month(pact_time) = month(getdate())
if(@sum3 is null)
set @sum3 = 0
insert into @tb1([type],[sum]) values('已回款金额',@sum3) select * from @tb1
------------------------------------------------------
-------------2. 热点客户
select top 10 cust_name,cust_id,cust_hot from custom where cust_netuid = @id and cust_hot is not null and cust_hot != '0' and cust_hot !='' order by cust_utime desc
--------------------------------------------------------
--------------3.公告信息select top 10 gong_id,gong_title from gonggao where gong_compid = @compid and DATEDIFF(day,getdate(),gong_totime)>0 order by gong_lv desc,gong_ctime desc--------4 代办任务信息
--代办状态 为 执行中 以及 已超期
select cust_name,task_id,task_title,[date] = cast(year(task_ftime) as nvarchar)+'-'+cast(month(task_ftime) as nvarchar)+'-'+cast(day(task_ftime) as nvarchar) ,taskstatelist_name from task left join custom on cust_id = task_custid left join taskstatelist on taskstatelist_id = task_stateID where (taskstatelist_name = '超期' or taskstatelist_name = '执行中') and task_execID = @id order by taskstatelist_name,task_prior
end 这个问题困扰我1个星期了..求教
set QUOTED_IDENTIFIER ON
goALTER proc [dbo].[getdefinfo]
(
--获取首页的信息
@compid char(11),
@id char(11)
)
as
begin
--1.获取合同,回款的月度信息
declare @tb1 table([type] nvarchar(10),[sum] int default 0)
declare @sum1 int --合同额
select @sum1 = sum(pact_sum) from pact where pact_netuid = @id and year(pact_time) = year(getdate()) and month(pact_time) = month(getdate())
if(@sum1 is null)
set @sum1 = 0
insert into @tb1([type],[sum]) values('合同总额',@sum1)
------------------------------------------------------------------------------------------
declare @sum2 int --预计回款额
select @sum2 = sum(back_money) from pact inner join backmoney on back_pactID = pact_id where pact_netuid = @id and year(pact_time) = year(getdate()) and month(pact_time) = month(getdate())
if(@sum2 is null)
set @sum2 = 0
insert into @tb1([type],[sum]) values('预计回款额',@sum2)
-------------------------------------------------------------------------------------------
declare @sum3 int --已回款额
select @sum3 = sum(pact_backsum) from pact where pact_netuid = @id and year(pact_time) = year(getdate()) and month(pact_time) = month(getdate())
if(@sum3 is null)
set @sum3 = 0
insert into @tb1([type],[sum]) values('已回款金额',@sum3) select * from @tb1
------------------------------------------------------
-------------2. 热点客户
select top 10 cust_name,cust_id,cust_hot from custom where cust_netuid = @id and cust_hot is not null and cust_hot != '0' and cust_hot !='' order by cust_utime desc
--------------------------------------------------------
--------------3.公告信息select top 10 gong_id,gong_title from gonggao where gong_compid = @compid and DATEDIFF(day,getdate(),gong_totime)>0 order by gong_lv desc,gong_ctime desc--------4 代办任务信息
--代办状态 为 执行中 以及 已超期
select cust_name,task_id,task_title,[date] = cast(year(task_ftime) as nvarchar)+'-'+cast(month(task_ftime) as nvarchar)+'-'+cast(day(task_ftime) as nvarchar) ,taskstatelist_name from task left join custom on cust_id = task_custid left join taskstatelist on taskstatelist_id = task_stateID where (taskstatelist_name = '超期' or taskstatelist_name = '执行中') and task_execID = @id order by taskstatelist_name,task_prior
end 这个问题困扰我1个星期了..求教
在查询分析器执行看看执行计划
这怎么讲,有时候可能因为数据库缓存原因,快了点,还有你的各方面呀,有时候你CPU使用大的时候,显然就会慢点啦