写了个存储过程，但查询很慢，优化建议
日期：2014-05-17 浏览次数：20521 次
写了个存储过程，但查询很慢，请教大家优化建议
如下：
------最佳解决方案--------------------
不用游标的话速度最少提高几倍。
------其他解决方案--------------------
如下：
CREATE PROCEDURE Finallenergy @enddatetime char(10),@sub char(10) AS
DECLARE @username varchar(50)
DECLARE @userid char(11)
DECLARE @moterid char(12)
DECLARE @strsinReadtime1 char(16)
DECLARE @fsinEnergy1 decimal(18,2)
IF not EXISTS (SELECT name FROM sysobjects
WHERE name ='Finalldianliang' )
begin
CREATE TABLE Finalldianliang (
[userid] [char] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[username] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[moterid] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[endengy] [decimal](18, 2) NULL,
[enddate] [char] (16) COLLATE Chinese_PRC_CI_AS NULL,
) ON [PRIMARY]
end
DECLARE userinfo CURSOR LOCAL FOR select username,userid,moterid from userprofile u,sub s where s.subid=u.sub and s.substation=@sub order by line,userid
OPEN userinfo
FETCH NEXT FROM userinfo INTO @username,@userid,@moterid
while(@@fetch_status = 0)
begin
set @strsinReadtime1=' '
set @fsinEnergy1=-1
select @strsinReadtime1=enddate,@fsinEnergy1=endengy from userdayengy where userid=@userid and moterid=@moterid and datetime=@enddatetime
if (@fsinEnergy1=-1) --如果没有，取前一天
begin
select @strsinReadtime1=enddate,@fsinEnergy1=endengy from userdayengy where userid=@userid and moterid=@moterid and datetime=dateadd(day,-1,@enddatetime)
end
if (@fsinEnergy1=-1) --如果没有，取后一天
begin
select @strsinReadtime1=enddate,@fsinEnergy1=endengy from userdayengy where userid=@userid and moterid=@moterid and datetime=dateadd(day,1,@enddatetime)
end
if (@fsinEnergy1=-1)
begin
set @enddatetime=@enddatetime+'-23-59'
end
if (@fsinEnergy1 != -1)
begin
insert into Finalldianliang (username,userid,moterid,enddate,endengy) values(@username,@userid,@moterid,@strsinReadtime1,@fsinEnergy1)
end
FETCH NEXT FROM userinfo INTO @username,@userid,@moterid
end
CLOSE userinfo
DEALLOCATE userinfo
GO
------最佳解决方案--------------------
不用游标的话速度最少提高几倍。
------其他解决方案--------------------
CREATE PROCEDURE Finallenergy
@enddatetime CHAR(10) ,
@sub CHAR(10)
AS
DECLARE @username VARCHAR(50)
DECLARE @userid CHAR(11)
DECLARE @moterid CHAR(12)
DECLARE @strsinReadtime1 CHAR(16)
&nbs
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
