一笔相对比较复杂的存储过程

昨天公司里有个很久以前的做个网站，由于当时设计时没有充分的时间，就只是实现了当时功能而已。

近期由于网站使用的数据库的数据库数据量超过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

 

转载于:https://www.cnblogs.com/Blog_SivenZhang/archive/2010/05/22/1741575.html