昨天公司里有个很久以前的做个网站，由于当时设计时没有充分的时间，就只是实现了当时功能而已。
近期由于网站使用的数据库的数据库数据量超过300W条，导致了现在使用网站提供的查询功能执行数度相当慢，查询一次需要接近需要2分钟的时间，这在用户使用时是无法接受的。所以不能经理要求我对这次查询进行优化。
我仔细的看了看以前写的程序，发现当时在查询时由于查询比较复杂，需要多次访问数据库，所以导致速度比较慢，再加上在超过300W条记录的表中多次查询，导致了查询需要接近2分钟。
我通过分析，发现在该超过300W条数据的中查询时大多是根据包含时间的字段，所以，我给该表建立了一个关于时间的索引，为了测试索引的性能，我就直接打开页面进行查询。发现时间还是非常慢的，不过有了很大的提高，接近1分钟就就出了数据。
由于多次访问数据库，我决定写一个存储过程，来进行一次访问数据库来提高查询速度。由于查询比较复杂我决定使用游标，但是发现游标相当比较占用资源，会影响数据，所以就改用临时表。具体代码如下，里面有注释，就不在这里进行详细说明了
代码
if exists(select * from dbo.sysindexes where name='FixingData_index')
drop index FixingData.FixingData_index
--建立索引
create index FixingData_index on dbo.FixingData(Fixing_Sn,ReadDate)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hzxscada_GetStatData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[hzxscada_GetStatData]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--建立存储过程
CREATE proc hzxscada_GetStatData
@Department_id nvarchar(8),
@Fixing_Sn int,
@firstDate datetime,
@lastDate datetime
as
/*创建一张临时表*/
create table #StatData
(
Fixing_Sn int,
Department_Name nvarchar(40),
PinZhi nvarchar(30),
FixingName nvarchar(64),
DeviceType nvarchar(1),
SNO nvarchar(50),
sumData4 numeric(14,1),
firstReadDate datetime,
firstData5 numeric(14,1),
lastReadDate datetime,
lastData5 numeric(14,1),
XiSHu nvarchar(10),
Natural nvarchar(1)
)
/*创建第二张临时表，用于存放符合条件的Fixing表中数据*/
create table #Fixing
(
[id] int identity(1,1) not null,
Fixing_Sn int,
Department_Name nvarchar(40),
PinZhi nvarchar(30),
FixingName nvarchar(64),
DeviceType nvarchar(1),
SNO nvarchar(50),
XiSHu nvarchar(10)
primary key(id)
)
/*定义变量*/
declare @Fixing_id int,@Natural nvarchar(1),@Department_Name nvarchar(40),@PinZhi nvarchar(30),@FixingName nvarchar(64),
@DeviceType nvarchar(1),@SNO nvarchar(50),@XiSHu nvarchar(10),
@firstReadDate datetime,@firstData5 numeric(14,1),@lastReadDate datetime,@lastData5 numeric(14,1),@sumData4 numeric(14,1)
--向第二张临时表中插入数据
if (@Fixing_Sn<>-1)
begin
insert into #Fixing (Fixing_Sn,Department_Name,PinZhi,FixingName,DeviceType,SNO,XiSHu)
(select Fixing_Sn,Department_Name,PinZhi,FixingName,DeviceType,SNO,XiSHu from Fixing
inner join Department on Department.Department_id=Fixing.Department_id
where Fixing.Department_id like (@Department_id+'%') and Fixing.Fixing_Sn = @Fixing_Sn
and Fixing.IsValid ='0' and fixtype ='6')
end
else
begin
insert into #Fixing (Fixing_Sn,Department_Name,PinZhi,FixingName,DeviceType,SNO,XiSHu)
(select Fixing_Sn,Department_Name,PinZhi,FixingName,DeviceType,SNO,XiSHu from Fixing
inner join Department on Department.Department_id=Fixing.Department_id
where Fixing.Department_id like (@Department_id+'%')
and Fixing.IsValid ='0' and fixtype ='6' )
end
declare @i int
set @i=0
while @i<(select count(id) from #Fixing)
begin
--获取临时表中当前记录的Fixing_Sn等记录
select @Fixing_id=Fixing_Sn,@Department_Name=Department_Name,@PinZhi=PinZhi,@FixingName=FixingName,
@DeviceType=DeviceType,@SNO=SNO,@XiSHu=XiSHu
from #Fixing where [id]=@i+1
--获取满足条件的“上次读数应抄时间”及“上次读数”
select top 1 @firstReadDate=ReadDate,@firstData5=Data5 from FixingData
where Fixing_Sn=@Fixing_id and ReadDate between @firstDate and @lastDate order by ReadDate
--获取满足条件的“本次读数应抄时间”及“本次读数”及“表状态”
select top 1 @lastReadDate=ReadDate,@lastData5=Data5,@Natural=Natural from FixingData
where Fixing_Sn=@Fixing_id and ReadDate between @firstDate and @lastDate order by ReadDate desc
--获取满足条件的“实际用量”
select @sumData4=sum(Data4) from FixingData where (Fixing_Sn=@Fixing_id) and (ReadDate between @firstDate and @lastDate) and Natural='0'
if(@sumData4 is null)
begin
set @sumData4=0.0
end
--将本条数据插入临时表中
insert into #StatData (Fixing_Sn,Department_Name,PinZhi,FixingName,DeviceType,SNO,sumData4,firstReadDate,
firstData5,lastReadDate,lastData5,XiSHu,Natural)
values (@Fixing_id,@Department_Name,@PinZhi,@FixingName,@DeviceType,@SNO,@sumData4,
@firstReadDate,@firstData5,@lastReadDate,@lastData5,@XiSHu,@Natural)
set @i = @i + 1
end
/*删除第二张临时表
drop table #Fixing*/
select Fixing_Sn,Department_Name,PinZhi,FixingName,DeviceType,SNO,XiSHu,firstReadDate,firstData5,lastReadDate,lastData5,sumData4,Natural from #StatData
order by Department_Name
/*删除这张临时表
drop table #StatData*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
