上次我提问的是联接查询，查询是查询出来了，但是执行还是有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<[email protected]
begin
select @MYL=SUM(Yuliang)from HistoryData where Date_Time >= @Month+'-01' and [email protected]
select @YYL=SUM(Yuliang)from HistoryData where Date_Time >= @Year+'-01-01' and [email protected]
select @ShuiWei=ShuiWei,@LiuLiang=LiuLiang,@LiuLiang_Total=LiuLiang_Total,@YuLiang=YuLiang,@date_time=Date_Time
from LastData where [email protected] order by SiteID
select @SiteName=SiteName from SiteInfo where [email protected] order by SiteID
insert into #tempLastdata values (@SelectIndex,@SiteName,@ShuiWei,@LiuLiang,@LiuLiang_Total,@YuLiang,@MYL,@YYL,@date_time)
set @[email protected]+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<[email protected] begin select @MYL=SUM(Yuliang) from HistoryData where Date_Time >= @Month+'-01' and [email protected] select @YYL=SUM(Yuliang) from HistoryData where Date_Time >= @Year+'-01-01' and [email protected] select @ShuiWei=ShuiWei, @LiuLiang=LiuLiang, @LiuLiang_Total=LiuLiang_Total, @YuLiang=YuLiang, @date_time=Date_Time from LastData where [email protected] order by SiteID select @SiteName=SiteName from SiteInfo where [email protected] order by SiteID insert into #tempLastdata values ( @SelectIndex, @SiteName, @ShuiWei, @LiuLiang, @LiuLiang_Total, @YuLiang, @MYL,@YYL, @date_time ) set @[email protected]+1 endselect * from #tempLastdata--把排版给楼主改了一下，我建议你提供循环里面那两张表的结构以及它们之间是否有关联条件如果存在的话看看有没有办法避免你这样写，比如说递归之类的写法