请问如何提高该存储过程的执行效率
上次我提问的是联接查询，查询是查询出来了，但是执行还是有3s左右的停顿才显示出数据，我现在用的是建立临时表的办法，数据也查出了，感觉上么，好像也快了点，想问下，在原来的基础上哈能优化吗？？？？

USE [GQ-QiaoYing]
GO
/****** Object: StoredProcedure [dbo].[SelectLastData] Script Date: 07/08/2012 08:14:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SelectLastData]

as
create table #tempLastdata
(SiteID int,
SiteName varchar(50),
ShuiWei varchar(50),
LiuLiang varchar(50),
LiuLiang_Total varchar(50),
YuLiang varchar(50),
MYL varchar(50),
YYL varchar(50),
date_time datetime )

declare @SiteID varchar(50),
@SiteName varchar(50),
@ShuiWei varchar(50),
@LiuLiang varchar(50),
@LiuLiang_Total varchar(50),
@YuLiang varchar(50),
@MYL varchar(50),
@YYL varchar(50),
@date_time datetime,
@Month varchar(50),
@Year varchar(50),
@SelectIndex int,
@count int

select @count=COUNT(*) from LastData
set @SelectIndex=1

Select @Month=MAX(CONVERT(varchar(7),date_time,120)),@Year=MAX(CONVERT(varchar(4),date_time,120)) from HistoryData
while @SelectIndex<=@count
begin
select @MYL=SUM(Yuliang)from HistoryData where Date_Time >= @Month+'-01' and SiteID=@SelectIndex
select @YYL=SUM(Yuliang)from HistoryData where Date_Time >= @Year+'-01-01' and SiteID=@SelectIndex
select @ShuiWei=ShuiWei,@LiuLiang=LiuLiang,@LiuLiang_Total=LiuLiang_Total,@YuLiang=YuLiang,@date_time=Date_Time
from LastData where SiteID=@SelectIndex order by SiteID
select @SiteName=SiteName from SiteInfo where SiteID=@SelectIndex order by SiteID
insert into #tempLastdata values (@SelectIndex,@SiteName,@ShuiWei,@LiuLiang,@LiuLiang_Total,@YuLiang,@MYL,@YYL,@date_time)
set @SelectIndex=@SelectIndex+1
end
select * from #tempLastdata

[解决办法]

Assembly code

--试下这个速度是多少alter proc [dbo].[SelectLastData]asdeclare @Month varchar(50),@Year varchar(50)Select @Month=MAX(CONVERT(varchar(7),date_time,120)),@Year=MAX(CONVERT(varchar(4),date_time,120)) from HistoryData;if object_id('tempdb..#tempLastdata') is not null drop table #tempLastdata;select SiteID,SiteName=convert(varchar(50),''),    ShuiWei,LiuLiang,LiuLiang_Total,YuLiang,    MYL=convert(varchar(50),''),    YYL=convert(varchar(50),''),    Date_Timeinto #tempLastdata from LastData where 1=2;insert into #tempLastdata    select SiteID,ShuiWei,LiuLiang,LiuLiang_Total,YuLiang,Date_Time    from LastData;if object_id('tempdb..#t1') is not null drop table #t1;select SiteID,sum(case when convert(varchar(7),date_time,120)>@Month then Yuliang    else 0 end) myl,    sum(Yuliang) yylinto #t1from HistoryData where convert(varchar(4),date_time,120) >= @Yeargroup by SiteID;update #tempLastdata    set MYL=#t1.myl,        YYL=#t1.yyl    from #t1    where #t1.SiteID=#tempLastdata.SiteID;update #tempLastdata    set SiteName=SiteInfo.SiteName    from SiteInfo     where #tempLastdata.SiteID=SiteInfo.SiteID;select * from #tempLastdata order by SiteID;drop table #tempLastdata,#t1;go
[解决办法]
SQL code
ALTER proc [dbo].[SelectLastData]ascreate table #tempLastdata(    SiteID int,    SiteName varchar(50),    ShuiWei varchar(50),    LiuLiang varchar(50),    LiuLiang_Total varchar(50),    YuLiang varchar(50),    MYL varchar(50),    YYL varchar(50),    date_time datetime )  declare @SiteID varchar(50),    @SiteName varchar(50),    @ShuiWei varchar(50),    @LiuLiang varchar(50),    @LiuLiang_Total varchar(50),    @YuLiang varchar(50),    @MYL varchar(50),    @YYL varchar(50),    @date_time datetime,    @Month varchar(50),    @Year varchar(50),    @SelectIndex int,    @count int  select     @count=COUNT(*) from     LastDataset @SelectIndex=1 Select     @Month=MAX(CONVERT(varchar(7),date_time,120)),    @Year=MAX(CONVERT(varchar(4),date_time,120)) from     HistoryDatawhile @SelectIndex<=@count    begin        select             @MYL=SUM(Yuliang)        from             HistoryData         where             Date_Time >= @Month+'-01'             and SiteID=@SelectIndex        select             @YYL=SUM(Yuliang)        from             HistoryData         where             Date_Time >= @Year+'-01-01'             and SiteID=@SelectIndex        select             @ShuiWei=ShuiWei,            @LiuLiang=LiuLiang,            @LiuLiang_Total=LiuLiang_Total,            @YuLiang=YuLiang,            @date_time=Date_Time        from             LastData         where             SiteID=@SelectIndex         order by             SiteID        select             @SiteName=SiteName         from             SiteInfo         where             SiteID=@SelectIndex         order by             SiteID        insert into             #tempLastdata         values             (                @SelectIndex,                @SiteName,                @ShuiWei,                @LiuLiang,                @LiuLiang_Total,                @YuLiang,                @MYL,@YYL,                @date_time            )        set @SelectIndex=@SelectIndex+1 endselect     * from     #tempLastdata--把排版给楼主改了一下，我建议你提供循环里面那两张表的结构以及它们之间是否有关联条件如果存在的话看看有没有办法避免你这样写，比如说递归之类的写法