salut am o problema cu codul asta...ma poate ajuta cineva, cum as putea sa optimizez ce incerc sa fac mai jos....un sfat ceva

declare @Data   varchar(20)
select @Data = convert(varchar, convert(datetime, dateadd(day, -1, getdate()), 112), 112)

if object_id('tempdb..#tmp_LGX ') is not null drop table #tmp_LGX
 create table #tmp_LGX
  (
   CustomerCode int,
   CZL  money
  )
insert into #tmp_LGX
select b.CUSTOMER, b.CZL
from DATA..LGX b
where  b.CUSTOMER is not null and b.JDATA >= dateadd(year,-1,@Data) and JDATA <= @Data

if object_id('tempdb..#tmp_LGX_J ') is not null drop table #tmp_LGX_J
 create table #tmp_LGX_J
  (
   CustomerCode int,
   Suma  money
  )
insert into #tmp_LGX_J
select b.CustomerCode, sum(b.CZL)/ case
     when datediff(month, min(a.OpeningDate), @Data) >= 1
      and datediff(month, min(a.OpeningDate), @Data) <= 12
      then datediff(month, min(a.OpeningDate), @Data)
     when datediff(month, min(a.OpeningDate), @Data) = 0 then 1
     else 12
     end
from #tmp_LGX b, TBL_ACC a
where b.CustomerCode = a.CustomerId
group by b.CustomerCode

update a set a.ValoareRulaj = b.Suma
from TEST a, #tmp_LGX_J b
where a.CustomerCode = b.CustomerCode

update a set Rulaj = case
   when a.ValoareRulaj < 200 and a.TipClient = 'PF' then 'sub 200 ron pe luna'
   when a.ValoareRulaj >= 200 and a.ValoareRulaj <= 500 and a.TipClient = 'PF' then 'intre 200 si 500 ron pe luna'
   when a.ValoareRulaj > 500 and a.TipClient = 'PF' then 'peste 500 ron pe luna'

   when a.ValoareRulaj < 400 and a.TipClient = 'PFA' then 'sub 400 ron pe luna'
   when a.ValoareRulaj >= 400 and a.ValoareRulaj <= 1000 and a.TipClient = 'PFA' then 'intre 400 si 1000 ron pe luna'
   when a.ValoareRulaj > 1000 and a.TipClient = 'PFA' then 'peste 1000 ron pe luna'

   when a.ValoareRulaj < 2000 and a.TipClient = 'PJ' then 'sub 2000 ron pe luna'
   when a.ValoareRulaj >= 2000 and a.ValoareRulaj <= 20000 and a.TipClient = 'PJ' then 'intre 2000 si 20000 ron pe luna'
   when a.ValoareRulaj > 20000 and a.TipClient = 'PJ' then 'peste 20000 ron pe luna'

   else ''
   end
from TEST a

mai specific ca tabela LGX de pe baza de date DATA (DATA..LGX) este f mare are 28 GB si vreo 30 milioane de inregistari.

vreun index pe tabele temporare ar ajuta?

multumesc.

alex.

Care dintre query-uri este cel mai lent (de fapt, cât durează fiecare query) ? Dacă cel problematic este primul UPDATE, atunci probabil un index pe coloana CustomerCode (în tabela #tmp_LGX_J şi în tabela TEST) ar fi foarte folositor. Bănuiesc că în tabela TBL_ACC există deja index pe CustomerCode (dacă nu există, ar trebui creat şi el). Cam câte rânduri (din cele 30 milioane) se inserează în prima tabelă temporară şi în a doua tabelă temporară ? În funcţie de asta, ar putea fi util să eliminăm una din tabelele temporare sau ambele şi să facem UPDATE-ul într-un singur pas.

Răzvan

Care dintre query-uri este cel mai lent (de fapt, cât durează fiecare query) ?
primul insert dureaza ~ 12 min
al 2-lea insert dureaza ~ 40 min
primul update dureaza ~ 1 sec
al 2-lea update e rapid ~ 1 sec
 
Dacă cel problematic este primul UPDATE, atunci probabil un index pe coloana CustomerCode (în tabela #tmp_LGX_J şi în tabela TEST) ar fi foarte folositor.
am creat: CREATE INDEX [ix_CustomerCode] ON [dbo].[#tmp_LGX_J]([CustomerCode]) WITH  FILLFACTOR = 90 ON [PRIMARY]
trebuie cumva sa-l creez dupa ce fac insertul in tabela asta temporara?
pe tabela TEST este creat index pe CustomerCode.

Bănuiesc că în tabela TBL_ACC există deja index pe CustomerCode (dacă nu există, ar trebui creat şi el).
DA. pe TBL_ACC exista index pe CustomerID
 
Cam câte rânduri (din cele 30 milioane) se inserează în prima tabelă temporară şi în a doua tabelă temporară ?
in prima tabela se insereaza 23.569.043 in a 2-a 47.553

mai specific si ca in tabela TEST nu am decat 30 de inregistrari dar pe live o sa am ~ 50.000, nici nu vreau sa ma gandesc atunci :(

multumesc.

alex.

Potzi sa setezi SET STATISTICS IO ON si sa rulezi, apoi sa postezi rezultatul? Deasemenea DDL-ul complet pentru DATA..LGX si DATA..TBL_ACC.

In primul rind schimba @data in DATETIME.

Din cite inteleg eu primul insert scaneaza complet DATA..LGX si din cele 30 mil. de inregistrari candidat, 23 mil. se califica sa fie inserate. Potzi sa te uitzi in planul de executie si sa ne spui ce metoda de access este aleasa pentru DATA..LGX? Ma astept ca este un clustered index scan. Daca tabela DATA..LGX este 'larga' (multe cimpuri, fiecare record are sute de bytes) atunci te-ar putea ajuta un covering index pe JDATA cu CUSTOMER si CZL included (acest index ar reduce numarul de pagini scanate). Daca LGX are recorduri mici, atunci te-ar putea ajuta sa-l organizezi sa fie clustered dupa JDATA (dar asta are implicatzii in alte operatiuni care le faci pe LGX care nu le putem stii din post).

A doua problema este al doilea insert, care banuiesc ca se face ca un nested loop (pentru fiecare TBL_ACC se scaneaza complet #tmp_LGX). Recomandarea mea ar fi sa adaugi un index pe #tmp_LGX pe CustomerCode, sau declara tabela #tmp_LGX ca fiind clustered dupa CustomerCode. Acest index ar ajuta foarte mult nested loop-ul respectiv. Trebuie sa experimentezi aici, sa vezi cum afecteaza primul insert, poate e mai bine sa lasi #tmp_LGX ca heap la inceput si apoi s-o convertesti intr-un cluster pe CustomerCode.

---

http://rusanu.com

Din cele scrise de tine structura incompletă a celor două tabele este:
TBL_ACC(...,CustomerId,OpeningDate,...)
LGX(...,CUSTOMER,CZL,JDATA,...)

O posibilă soluţie PARŢIALĂ ar fi să denormalizezi schema bazei de date.
Dacă între cele două tabele ai o legătură de tip 1 =>> n(mai multe) la modul LGX 1 =>> n TBL_ACC atunci adaugi în TBL_ACC câmpurile din LGX care prezintă interes şi vei obţine:
TBL_ACC(...,CustomerId,OpeingDate,...,CZL,JDATA), denormalizare însoţită de implementarea unor proceduri sub formă de trigger (declanşator) pe LGX care să actualizeze CZL şi JDATA din TBL_ACC.

Posibilul avantaj: scapi de joncţiunea de la al doilea INSERT.