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

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

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