游标改为WHILE语句求助!!!在线等!

Question

本人写了一个存储过程！不知是否数据太大，还是自己写的性能太差，无法查询出结果，现在想把里面的游标换成WHILE语句不知道会不会提高一些性能！！或者高人有更好的优化方式请辅导辅导！代码如下CREATE  PROCEDURE [dbo].[GetTicketStock]
@type nvarchar(100),
@BeginTime datetime,
@EndTime datetime,
@AgentID int
 AS
if(@type=-1) set @type=null
if(@AgentID=-1) set @AgentID=null
declare @TempTable table
(
pkid int Identity(1,1),
type varchar(100),
lastcount int,
lastprice float,
nowcount int,
nowprice float,
thissellcount int,
thissellprice float,
thiscount int,
thisprice float
)
declare @lastcount int
declare @lastprice float
declare @nowcount int
declare @nowprice float
declare @thissellcount int
declare @thissellprice float
declare @thiscount int
declare @thisprice floatselect count(*) as lastcount,sum(TicketAgentPrice) as lastprice,TicketType as tickettype into #lasttable from manageticketdata where datediff(day,BeginTime,@BeginTime)>=0 and TicketType=COALESCE(@type,tickettype) and TicketAgentID=COALESCE(@AgentID,TicketAgentID) group by tickettype
select count(*) as nowcount,sum(TicketAgentPrice) as nowprice,TicketType as tickettype into #nowtable from manageticketdata where datediff(day,BeginTime,@BeginTime)<=0 and datediff(day,BeginTime,@EndTime)>=0 and TicketType=COALESCE(@type,tickettype) and TicketAgentID=COALESCE(@AgentID,TicketAgentID) group by tickettype
select count(*) as thiscount,sum(TicketAgentPrice) as thisprice,TicketType as tickettype into #thistable from manageticketdata where  datediff(day,EndTime,@BeginTime)<=0 and datediff(day,EndTime,@EndTime)>=0 and TicketType=COALESCE(@type,tickettype) and TicketAgentID=COALESCE(@AgentID,TicketAgentID) group by tickettypedeclare cur cursor
for  select NodeID from ManageTicketType where (@type is null or NodeParentDath like '%'+@type+'%') or NodeID=COALESCE(@type,NodeID) 
open cur
declare @typeid varchar(50)
FETCH NEXT FROM cur into @typeid
While(@@FETCH_STATUS=0)
begin

select @lastcount=lastcount,@lastprice=lastprice from #lasttable where tickettype in (select NodeID from ManageTicketType where NodeParentDath like '%'+@typeid+'%')
select @lastcount=nowcount,@lastprice=nowprice from #nowtable where tickettype in (select NodeID from ManageTicketType where NodeParentDath like '%'+@typeid+'%')
select @lastcount=thiscount,@lastprice=thisprice from #thistable where tickettype in (select NodeID from ManageTicketType where NodeParentDath like '%'+@typeid+'%')
set @thiscount=@lastcount+@nowcount-@thissellcount
set @thisprice=@lastprice+@nowprice-@thissellprice
insert @TempTable(type,lastcount,lastprice,nowcount,nowprice,thissellcount,thissellprice,thiscount,thisprice)
values( @typeid,@lastcount, @lastprice,@nowcount,@nowprice,@thissellcount,@thissellprice,@thiscount,@thisprice)
end
close cur
deallocate curdrop table #lasttable
drop table #nowtable
drop table #thistableselect * from @TempTable
GO

Answer 1

此处的赋值似乎有问题，先按我的理解简单优化一下：
CREATE  PROCEDURE [dbo].[GetTicketStock]
@type nvarchar(100),
@BeginTime datetime,
@EndTime datetime,
@AgentID int
AS
begin  if(@type=-1) 
    set @type=null
      
  if(@AgentID=-1) 
    set @AgentID=null
  
  declare @TempTable table
  (
    pkid int Identity(1,1),
    type varchar(100),
    lastcount int,
    lastprice float,
    nowcount int,
    nowprice float,
    thissellcount int,
    thissellprice float,
    thiscount int,
    thisprice float    
  )
  
  declare @lastcount int
  declare @lastprice float
  declare @nowcount int
  declare @nowprice float
  declare @thissellcount int
  declare @thissellprice float
  declare @thiscount int
  declare @thisprice float
  
  select
    TicketType as tickettype,
    sum(case when datediff(day,BeginTime,@BeginTime)>=0                                         then 1                else 0 end) as lastcount,
    sum(case when datediff(day,BeginTime,@BeginTime)>=0                                         then TicketAgentPrice else 0 end) as lastprice,
    sum(case when datediff(day,BeginTime,@BeginTime)<=0 and datediff(day,BeginTime,@EndTime)>=0 then 1                else 0 end) as nowcount ,
    sum(case when datediff(day,BeginTime,@BeginTime)<=0 and datediff(day,BeginTime,@EndTime)>=0 then TicketAgentPrice else 0 end) as nowprice ,
    sum(case when datediff(day,EndTime,@BeginTime)<=0   and datediff(day,EndTime,@EndTime)>=0   then 1                else 0 end) as thiscount,
    sum(case when datediff(day,EndTime,@BeginTime)<=0   and datediff(day,EndTime,@EndTime)>=0   then TicketAgentPrice else 0 end) as thisprice 
  into #
  from
    manageticketdata
  where 
    TicketType=COALESCE(@type,tickettype) 
    and 
    TicketAgentID=COALESCE(@AgentID,TicketAgentID) 
  group by 
    tickettype
  
  
  declare cur cursor for  
  select NodeID from ManageTicketType where (@type is null or NodeParentDath like '%'+@type+'%') or NodeID=COALESCE(@type,NodeID) 
  
  open cur
  
  declare @typeid varchar(50)
  
  FETCH NEXT FROM cur into @typeid
  
  While(@@FETCH_STATUS=0)
  begin
      select 
          @lastcount    =lastcount,
          @lastprice    =lastprice,
          @nowcount     =nowcount ,
          @nowprice     =nowprice ,
          @thissellcount=thiscount,
          @thissellprice=thisprice 
      from 
          # 
      where 
          tickettype in (select NodeID from ManageTicketType where NodeParentDath like '%'+@typeid+'%')
      
      set @thiscount=@lastcount+@nowcount-@thissellcount
      set @thisprice=@lastprice+@nowprice-@thissellprice
      
      insert @TempTable(type,lastcount,lastprice,nowcount,nowprice,thissellcount,thissellprice,thiscount,thisprice)
      values(@typeid,@lastcount, @lastprice,@nowcount,@nowprice,@thissellcount,@thissellprice,@thiscount,@thisprice)
  end
  
  close cur
  deallocate cur
  
  drop table #
  
  select * from @TempTable
end
GO

Answer 2

表结构.里面数据挺多的。估计有40多万条数据!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ManageTicketData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ManageTicketData]
GOCREATE TABLE [dbo].[ManageTicketData] (
[TicketID] [int] IDENTITY (1, 1) NOT NULL ,
[TicketType] [int] NOT NULL ,
[TicketNumber] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[TicketCost] [float] NOT NULL ,
[TicketFactPrice] [float] NOT NULL ,
[TicketFettle] [int] NOT NULL ,
[TicketSupperID] [int] NOT NULL ,
[TicketAgentID] [int] NOT NULL ,
[BeginTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
[BeginBatch] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[EndBatch] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[AgentPayment] [int] NOT NULL ,
[Re] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[AgentPaymentID] [int] NOT NULL ,
[AgentPaymetTime] [datetime] NOT NULL ,
[AgentPaymetNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SupplierPayment] [int] NOT NULL ,
[SupplierPaymentID] [int] NOT NULL ,
[SupplierPaymetTime] [datetime] NOT NULL ,
[SupplierPaymetNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PreFettle] [int] NOT NULL ,
[TicketAgentPrice] [float] NOT NULL ,
[OrderOP] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[TicketOpenPrice] [float] NOT NULL ,
[OrderSource] [int] NOT NULL ,
[MoneyType] [int] NOT NULL ,
[MoneySum] [float] NOT NULL ,
[LockedFettle] [int] NOT NULL ,
[ReckoningFettle] [int] NOT NULL ,
[AgentDepID] [int] NOT NULL ,
[TicketCostState] [int] NOT NULL 
) ON [PRIMARY]
GO弱弱问下测试数据怎么贴出？？期待高人。

Answer 3

把表里的数据，导出sql插入脚本（可以使用代码生成器导出，也可以借助其他工具）
insert into ManageTicketData 
select ...,...,...,...,等

Answer 4

存储过程有点小小改动。。从新贴下CREATE  PROCEDURE [dbo].[GetTicketStock]
@type nvarchar(100),
@BeginTime datetime,
@EndTime datetime,
@AgentID int
 AS
if(@type=-1) set @type=null
if(@AgentID=-1) set @AgentID=null
declare @TempTable table
(
pkid int Identity(1,1),
type varchar(100),
lastcount int,
lastprice float,
nowcount int,
nowprice float,
thissellcount int,
thissellprice float,
thiscount int,
thisprice float
)
declare @lastcount int
declare @lastprice float
declare @nowcount int
declare @nowprice float
declare @thissellcount int
declare @thissellprice float
declare @thiscount int
declare @thisprice floatselect count(*) as lastcount,sum(TicketAgentPrice) as lastprice,TicketType as tickettype into #lasttable from manageticketdata where datediff(day,BeginTime,@BeginTime)>=0 and TicketType=COALESCE(@type,tickettype) and TicketAgentID=COALESCE(@AgentID,TicketAgentID) group by tickettype
select count(*) as nowcount,sum(TicketAgentPrice) as nowprice,TicketType as tickettype into #nowtable from manageticketdata where datediff(day,BeginTime,@BeginTime)<=0 and datediff(day,BeginTime,@EndTime)>=0 and TicketType=COALESCE(@type,tickettype) and TicketAgentID=COALESCE(@AgentID,TicketAgentID) group by tickettype
select count(*) as thiscount,sum(TicketAgentPrice) as thisprice,TicketType as tickettype into #thistable from manageticketdata where  datediff(day,EndTime,@BeginTime)<=0  and datediff(day,EndTime,@EndTime)>=0 and TicketFettle=1 and TicketType=COALESCE(@type,tickettype) and TicketAgentID=COALESCE(@AgentID,TicketAgentID) group by tickettypedeclare cur cursor
for  select NodeID from ManageTicketType where (@type is null or NodeParentDath like '%'+@type+'%') or NodeID=COALESCE(@type,NodeID) 
open cur
declare @typeid varchar(50)
FETCH NEXT FROM cur into @typeid
While(@@FETCH_STATUS=0)
begin

select @lastcount=sum(lastcount),@lastprice=sum(lastprice) from #lasttable where tickettype in (select NodeID from ManageTicketType where NodeParentDath like '%'+@typeid+'%')
select @lastcount=sum(nowcount),@lastprice=sum(nowprice) from #nowtable where tickettype in (select NodeID from ManageTicketType where NodeParentDath like '%'+@typeid+'%')
select @lastcount=sum(thiscount),@lastprice=sum(thisprice) from #thistable where tickettype in (select NodeID from ManageTicketType where NodeParentDath like '%'+@typeid+'%')
set @thiscount=@lastcount+@nowcount-@thissellcount
set @thisprice=@lastprice+@nowprice-@thissellprice
insert @TempTable(type,lastcount,lastprice,nowcount,nowprice,thissellcount,thissellprice,thiscount,thisprice)
values( @typeid,@lastcount, @lastprice,@nowcount,@nowprice,@thissellcount,@thissellprice,@thiscount,@thisprice)
end
close cur
deallocate curdrop table #lasttable
drop table #nowtable
drop table #thistableselect * from @TempTable
GO

Answer 5

select 
    count(*) as thiscount,sum(TicketAgentPrice) as thisprice,TicketType as tickettype 
into 
    #thistable 
from 
    manageticketdata 
where  
    datediff(day,EndTime,@BeginTime)<=0  and datediff(day,EndTime,@EndTime)>=0 
    and 
    TicketFettle=1    --差异仅在这一句?
    and 
    TicketType=COALESCE(@type,tickettype) and TicketAgentID=COALESCE(@AgentID,TicketAgentID) 
group by 
    tickettype

Answer 6

抑郁了一个下午，ISP的故障导致网络中断了。
CREATE  PROCEDURE [dbo].[GetTicketStock]
@type nvarchar(100),
@BeginTime datetime,
@EndTime datetime,
@AgentID int
AS
begin  if(@type=-1) 
    set @type=null
      
  if(@AgentID=-1) 
    set @AgentID=null
  
  declare @TempTable table
  (
    pkid int Identity(1,1),
    type varchar(100),
    lastcount int,
    lastprice float,
    nowcount int,
    nowprice float,
    thissellcount int,
    thissellprice float,
    thiscount int,
    thisprice float    
  )
  
  declare @lastcount int
  declare @lastprice float
  declare @nowcount int
  declare @nowprice float
  declare @thissellcount int
  declare @thissellprice float
  declare @thiscount int
  declare @thisprice float
  
  select
    TicketType as tickettype,
    sum(case when datediff(day,BeginTime,@BeginTime)>=0                                                        then 1                else 0 end) as lastcount,
    sum(case when datediff(day,BeginTime,@BeginTime)>=0                                                        then TicketAgentPrice else 0 end) as lastprice,
    sum(case when datediff(day,BeginTime,@BeginTime)<=0 and datediff(day,BeginTime,@EndTime)>=0                then 1                else 0 end) as nowcount ,
    sum(case when datediff(day,BeginTime,@BeginTime)<=0 and datediff(day,BeginTime,@EndTime)>=0                then TicketAgentPrice else 0 end) as nowprice ,
    sum(case when datediff(day,EndTime,@BeginTime)<=0 and datediff(day,EndTime,@EndTime)>=0 and TicketFettle=1 then 1                else 0 end) as thiscount,
    sum(case when datediff(day,EndTime,@BeginTime)<=0 and datediff(day,EndTime,@EndTime)>=0 and TicketFettle=1 then TicketAgentPrice else 0 end) as thisprice 
  into #
  from
    manageticketdata
  where 
    TicketType=COALESCE(@type,tickettype) 
    and 
    TicketAgentID=COALESCE(@AgentID,TicketAgentID) 
  group by 
    tickettype
  
  
  declare cur cursor for  
  select NodeID from ManageTicketType where (@type is null or NodeParentDath like '%'+@type+'%') or NodeID=COALESCE(@type,NodeID) 
  
  open cur
  
  declare @typeid varchar(50)
  
  FETCH NEXT FROM cur into @typeid
  
  While(@@FETCH_STATUS=0)
  begin
      select 
          @lastcount    =lastcount,
          @lastprice    =lastprice,
          @nowcount     =nowcount ,
          @nowprice     =nowprice ,
          @thissellcount=thiscount,
          @thissellprice=thisprice 
      from 
          # 
      where 
          tickettype in (select NodeID from ManageTicketType where NodeParentDath like '%'+@typeid+'%')
      
      set @thiscount=@lastcount+@nowcount-@thissellcount
      set @thisprice=@lastprice+@nowprice-@thissellprice
      
      insert @TempTable(type,lastcount,lastprice,nowcount,nowprice,thissellcount,thissellprice,thiscount,thisprice)
      values(@typeid,@lastcount, @lastprice,@nowcount,@nowprice,@thissellcount,@thissellprice,@thiscount,@thisprice)
  end
  
  close cur
  deallocate cur
  
  drop table #
  
  select * from @TempTable
end
GO