高手帮看下,如何优化下面的代码,现在代码执行时间太长。(循环用的太多,效率太低)CREATE PROCEDURE [dbo].[p_test] AS declare @v_kpi_code varchar(20)
declare @v_begin_date varchar(10)
declare @v_end_date varchar(10)
declare @v_int int
declare @v_corp varchar(10)
declare cur_corp cursor for
select corp_id from dim_corp
declare my_cur cursor for
select kpi_e from KPI_Relation
set @v_end_date = replace(CONVERT(VARCHAR(7),GETDATE(),120),'-','') exec('TRUNCATE TABLE fact_fina_rep_cw')
open cur_corp
fetch next from cur_corp into @v_corp
while @@FETCH_STATUS = 0
begin
OPEN my_cur
FETCH NEXT FROM my_cur INTO @v_kpi_code
while @@FETCH_STATUS = 0
begin
set @v_begin_date = '201001'
while @v_begin_date<=@v_end_date
begin
select @v_int = count(*) from fact_fina_rep where time_id = @v_begin_date and item_type >1 and item_id = @v_kpi_code and Corp_Id = @v_corp
if @v_int >0
begin
insert into fact_fina_rep_cw
select a.time_id,a.corp_id,@v_kpi_code,a.unit_id,a.item_value/10000,a.item_type,a.ver
from fact_fina_rep as a
where a.time_id = @v_begin_date
and a.item_type > 1 and a.item_type <>10
and item_id = @v_kpi_code
and Corp_Id = @v_corp
insert into fact_fina_rep_cw
select a.time_id,a.corp_id,@v_kpi_code,a.unit_id,a.item_value,a.item_type,a.ver
from fact_fina_rep as a
where a.time_id = @v_begin_date
and a.item_type =10
and item_id = @v_kpi_code
and Corp_Id = @v_corp
end
else
begin
insert into fact_fina_rep_cw
select a.time_id,a.corp_id,@v_kpi_code,a.unit_id,a.item_value,a.item_type,a.ver
from fact_fina_rep as a
where a.time_id = @v_begin_date
and a.item_type = 1
and item_id = replace(@v_kpi_code,'YB','')
and Corp_Id = @v_corp
end
set @v_begin_date = replace(convert(varchar(7),dateadd(m,1,convert(datetime,@v_begin_date+'01')),120),'-','')
end
FETCH NEXT FROM my_cur INTO @v_kpi_code
end
CLOSE my_cur
fetch next from cur_corp into @v_corp
end
close cur_corp
DEALLOCATE my_cur
DEALLOCATE cur_corp
GO
declare @v_begin_date varchar(10)
declare @v_end_date varchar(10)
declare @v_int int
declare @v_corp varchar(10)
declare cur_corp cursor for
select corp_id from dim_corp
declare my_cur cursor for
select kpi_e from KPI_Relation
set @v_end_date = replace(CONVERT(VARCHAR(7),GETDATE(),120),'-','') exec('TRUNCATE TABLE fact_fina_rep_cw')
open cur_corp
fetch next from cur_corp into @v_corp
while @@FETCH_STATUS = 0
begin
OPEN my_cur
FETCH NEXT FROM my_cur INTO @v_kpi_code
while @@FETCH_STATUS = 0
begin
set @v_begin_date = '201001'
while @v_begin_date<=@v_end_date
begin
select @v_int = count(*) from fact_fina_rep where time_id = @v_begin_date and item_type >1 and item_id = @v_kpi_code and Corp_Id = @v_corp
if @v_int >0
begin
insert into fact_fina_rep_cw
select a.time_id,a.corp_id,@v_kpi_code,a.unit_id,a.item_value/10000,a.item_type,a.ver
from fact_fina_rep as a
where a.time_id = @v_begin_date
and a.item_type > 1 and a.item_type <>10
and item_id = @v_kpi_code
and Corp_Id = @v_corp
insert into fact_fina_rep_cw
select a.time_id,a.corp_id,@v_kpi_code,a.unit_id,a.item_value,a.item_type,a.ver
from fact_fina_rep as a
where a.time_id = @v_begin_date
and a.item_type =10
and item_id = @v_kpi_code
and Corp_Id = @v_corp
end
else
begin
insert into fact_fina_rep_cw
select a.time_id,a.corp_id,@v_kpi_code,a.unit_id,a.item_value,a.item_type,a.ver
from fact_fina_rep as a
where a.time_id = @v_begin_date
and a.item_type = 1
and item_id = replace(@v_kpi_code,'YB','')
and Corp_Id = @v_corp
end
set @v_begin_date = replace(convert(varchar(7),dateadd(m,1,convert(datetime,@v_begin_date+'01')),120),'-','')
end
FETCH NEXT FROM my_cur INTO @v_kpi_code
end
CLOSE my_cur
fetch next from cur_corp into @v_corp
end
close cur_corp
DEALLOCATE my_cur
DEALLOCATE cur_corp
GO
set @v_begin_date = '201001'insert into
fact_fina_rep_cw
select b.time_id, a.corp_id,
isnull(c.kpi_e, e.kpi_e),
isnull(d.unit_id, f.unit_id),
case d.item_type when 10 then d.item_value/10000 else isnull(d.item_value, f.item_value) end,
isnull(d.item_type, f.item_type),
isnull(d.ver, f.ver)
from
dim_corp a
cross join
(
select @v_begin_date+number time_id from master..spt_values where type='P' and number<=datediff(mm,@v_begin_date+'01',getdate())
) b
left join
(
KPI_Relation c inner join fact_fina_rep d on c.kpi_e=d.item_id and d.item_type>1
) on a.corp_id=d.Corp_Id and b.time_id=d.time_id
left join
(
KPI_Relation e inner join fact_fina_rep f on replace(e.kpi_e,'YB','')=f.item_id and f.item_type=1
) on a.corp_id=f.Corp_Id and b.time_id=f.time_id
where
isnull(c.kpi_e, e.kpi_e) is not null