Pertanyaan Mengoptimalkan agregasi data cabang pohon di SQL Server 2008 (rekursi)


Saya memiliki tabel yang berisi tahapan dan sub-tahap proyek-proyek tertentu, dan meja dengan tugas-tugas khusus dan perkiraan biaya.
Saya perlu beberapa cara untuk mengumpulkan setiap level (tahap / sub-tahap), untuk melihat berapa biayanya, tetapi untuk melakukannya dengan biaya kinerja minimum.

Untuk mengilustrasikan ini, saya akan menggunakan struktur data berikut:

CREATE TABLE stage
(
    id int not null,
    fk_parent int
)

CREATE TABLE task
(
    id int not null,
    fk_stage int not null,
    cost decimal(18,2) not null default 0
)

dengan data berikut:

==stage==
id  fk_parent
1   null
2   1
3   1

==task==
id  fk_stage  cost
1   2         100
1   2         200
1   3         600

Saya ingin mendapatkan tabel yang berisi total biaya pada setiap cabang. Sesuatu seperti ini:

Stage ID      Total Cost
1             900
2             300
3             600

Tapi, saya juga ingin itu menjadi produktif. Saya tidak ingin berakhir dengan solusi yang sangat buruk seperti Algoritma terburuk di dunia. Maksud saya, inilah masalahnya. Dalam hal ini saya akan meminta data untuk semua item dalam stage tabel, dengan total biaya, masing-masing total biaya akan dievaluasi D kali, di mana D adalah kedalaman di pohon (level) di mana ia berada. Saya takut saya akan memukul kinerja yang sangat rendah pada sejumlah besar data dengan banyak level.

BEGITU,

Saya memutuskan untuk melakukan sesuatu yang membuat saya mengajukan pertanyaan ini di sini.
Saya memutuskan untuk menambahkan 2 kolom lagi ke stage tabel, untuk caching.

...
calculated_cost decimal(18,2),
date_calculated_cost datetime
...

Jadi yang ingin saya lakukan adalah meneruskan variabel lain dalam kode, a datetime nilai yang sama dengan waktu ketika proses ini dimulai (cukup banyak unik). Dengan begitu, jika stage baris sudah memiliki date_calculated_cost yang sama dengan yang saya bawa, saya tidak perlu menghitungnya lagi, dan kembalikan saja calculated_cost nilai.

Saya tidak bisa melakukannya dengan Fungsi (pembaruan diperlukan untuk stage tabel, setelah biaya dihitung)
Saya tidak bisa melakukannya dengan Prosedur (rekursi dalam menjalankan kursor adalah tidak ada jalan)
Saya tidak yakin tabel sementara cocok karena tidak akan memungkinkan permintaan bersamaan untuk prosedur yang sama (yang kemungkinan kecil, tapi bagaimanapun saya ingin melakukannya dengan cara yang benar)
Saya tidak tahu cara lain.

Saya tidak mengharapkan jawaban pasti untuk pertanyaan saya, tetapi saya akan memberi imbalan ide yang bagus, dan yang terbaik akan dipilih sebagai jawabannya.


5
2017-08-10 16:21


asal


Jawaban:


1. Cara untuk meng-query tabel untuk mendapatkan biaya gabungan.

  1. Hitung biaya untuk setiap tahap.
  2. Gunakan CTE rekursif untuk mendapatkan level untuk setiap tahap.
  3. Simpan hasilnya dalam tabel temp.
  4. Tambahkan beberapa indeks ke tabel temp.
  5. Perbarui biaya dalam tabel temp di loop untuk setiap level

Tiga langkah pertama digabungkan menjadi satu pernyataan. Mungkin bagus untuk kinerja untuk melakukan perhitungan pertama, cteCost, ke tabel temp itu sendiri dan menggunakan tabel temp di rekursif cteLevel.

;with cteCost as
(
  select s.id,
         s.fk_parent,
         isnull(sum(t.cost), 0) as cost
  from stage as s
    left outer join task as t
      on s.id = t.fk_stage
  group by s.id, s.fk_parent
),
cteLevel as
(
  select cc.id,
         cc.fk_parent,
         cc.cost,
         1 as lvl
  from cteCost as cc
  where cc.fk_parent is null
  union all
  select cc.id,
         cc.fk_parent,
         cc.cost,
         lvl+1
  from cteCost as cc
    inner join cteLevel as cl
      on cc.fk_parent = cl.id       
)
select *
into #task
from cteLevel

create clustered index IX_id on #task (id)
create index IX_lvl on #task (lvl, fk_parent)

declare @lvl  int
select @lvl = max(lvl)
from #task

while @lvl > 0
begin

  update T1 set
    T1.cost = T1.cost + T2.cost
  from #task as T1
    inner join (select fk_parent, sum(cost) as cost
                from #task
                where lvl = @lvl
                group by fk_parent) as T2
      on T1.id = T2.fk_parent

  set @lvl = @lvl - 1
end

select id as [Stage ID],
       cost as [Total Cost] 
from #task

drop table #task

2. Pemicu di atas meja task yang mempertahankan a calculated_cost lapangan di stage.

create trigger tr_task 
on task 
after insert, update, delete
as
  -- Table to hold the updates
  declare @T table
  (
    id int not null, 
    cost decimal(18,2) not null default 0
  )

  -- Get the updates from inserted and deleted tables
  insert into @T (id, cost)
  select fk_stage, sum(cost)
  from (
          select fk_stage, cost
          from inserted
          union all
          select fk_stage, -cost
          from deleted
       ) as T   
  group by fk_stage

  declare @id int
  select @id = min(id)
  from @T

  -- For each updated row
  while @id is not null
  begin

    -- Recursive update of stage
    with cte as 
    (
      select s.id,
             s.fk_parent
      from stage as s
      where id = @id
      union all
      select s.id,
             s.fk_parent
      from stage as s
        inner join cte as c
          on s.id = c.fk_parent    
    )
    update s set
      calculated_cost = s.calculated_cost + t.cost 
    from stage as s
      inner join cte as c
        on s.id = c.id
      cross apply (select cost
                   from @T
                   where id = @id) as t   

    -- Get the next id
    select @id = min(id)
    from @T
    where id > @id
  end

2
2017-08-11 06:47