那就建个nocluster 索引试试:
CREATE NONCLUSTERED INDEX [_dta_index_xfb_201009091035__K1] ON [xfb]
(
[cost_datetime] ASC
)
INCLUDE ( [cost_price]) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [_dta_index_xfb_201009091035__K1] ON [xfb]
(
[cost_datetime] ASC
)
INCLUDE ( [cost_price]) ON [PRIMARY]
go
ALTER proc [dbo].[usp_rpt_11] @stdate datetime ,@endate datetime
as
create table #t(sdate datetime not null,
f2total numeric(18,2) null,
f1_br numeric(18,2) null,
f1_lu numeric(18,2) null,
f1_su numeric(18,2) null)
insert into #t (sdate,f1_lu)
select convert(varchar(10),cost_datetime,120),sum(cost_price) from xfb
where convert(varchar(10),cost_datetime,120) between @stdate and @endate and
convert(varchar(100),cost_datetime,24) between '10:30:00' and '14:00:00'
group by convert(varchar(10),cost_datetime,120) --午餐--早餐
select convert(varchar(10),cost_datetime,120) as sdate,sum(cost_price) as brje into #br from xfb
where convert(varchar(10),cost_datetime,120) between @stdate and @endate and
convert(varchar(100),cost_datetime,24) between '05:00:00' and '10:00:00'
group by convert(varchar(10),cost_datetime,120)select convert(varchar(10),cost_datetime,120) as sdate,sum(cost_price) as f2je into #f2 from xfb
where convert(varchar(10),cost_datetime,120) between @stdate and @endate and
cast(cost_category as int) >=20 and cast(cost_category as int)<=30
group by convert(varchar(10),cost_datetime,120)
--晚餐
select convert(varchar(10),cost_datetime,120) as sdate,sum(cost_price) as suje into #su from xfb
where convert(varchar(10),cost_datetime,120) between @stdate and @endate and
convert(varchar(100),cost_datetime,24) between '16:00:00' and '20:00:00'
group by convert(varchar(10),cost_datetime,120)
update #t set f1_br=#br.brje from #br where #t.sdate=#br.sdate
update #t set f2total=#f2.f2je from #f2 where #t.sdate=#f2.sdate
update #t set f1_su=#su.suje from #su where #t.sdate=#su.sdate
select * from #t order by sdate-- usp_rpt_area_balance '2010-08-21','2010-08-31'表结构
CREATE TABLE [dbo].xfb(
[Cost_Card_ID] [varchar](20) NOT NULL,
[Cost_Employee_ID] [varchar](20) NOT NULL,
[Cost_Describe_ID] [varchar](4) NULL,
[Cost_Category] [varchar](30) NOT NULL,
[Cost_Count] [int] NOT NULL,
[Cost_Price] [money] NULL,
[Cost_Discount] [money] NULL,
[Cost_DateTime] [datetime] NOT NULL,
[Cost_Position_ID] [varchar](2) NULL,
[Cost_Remain_Cash] [money] NULL,
CONSTRAINT [pk_Consume_Source] PRIMARY KEY CLUSTERED
(
[Cost_Card_ID] ASC,
[Cost_Count] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]GO
sum(case when convert(varchar(100),cost_datetime,24) between '05:00:00' and '10:00:00' then cost_price
else 0
end) as brje,--早餐
sum(case when convert(varchar(100),cost_datetime,24) between '10:30:00' and '14:00:00' then cost_price
else 0
end) as luje,--午餐
sum(case when convert(varchar(100),cost_datetime,24) between '16:00:00' and '20:00:00' then cost_price
else 0
end) as suje --晚餐
into #AllJe
from xfb
where cost_datetime between convert(datetime,@stdate,120) and convert(datetime,@endate,120)
group by convert(varchar(10),cost_datetime,120)