写了个存储过程，但查询很慢，请教大家优化建议
如下：
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)
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 , --1 这个地方查询出来的数据行数，直接影响了LZ后面的循环，查看是否可用集合的方式处理
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
select userid,moterid from userprofile u,sub s where s.subid=u.sub and s.substation=sub order by line,userid
