Am urmatorul SQL:


select  left(fc.cont,1) as clasa,left(fc.cont,2) as subcls,
    isnull((
   
    SELECT sum(debit-credit) as sold
    FROM A_lstFisaCont
    where cont=fc.cont and
    ((datainr is not null and (fc.datainr>datainr or (fc.datainr=datainr and fc.nds>=nds))) or datainr is null) and
    coduser=1

    ),0) as sold, fc.debit-fc.credit-fc.sid+fc.sic as sSold,fc.debit-fc.sid as sdebit,fc.credit-fc.sic as scredit

from A_lstFisaCont FC
where fc.coduser=1
order by fc.cont,fc.datainr,fc.NDS


Daca tabela A_lstFisaContcontine 50.000 de inregistrari, SQL-ul returneaza inregistrarile dupa aproximativ 4 minute (sau mai mult, in functie de cat de incarcat mi-e procesorul de la celelalte aplicatii deschise), ceea ce e foarte mult. Am calculator performant, nu asta este important; important este ca trebuie sa fac ca rezultatul generat sa iasa mai repede, fie prin rescrierea SQL-ului, fie prin regandirea algoritmului de calcul al campului Sold (aici e problema). Am incercat sa pun indecsi pe tabela, am folosit inclusiv Index Tuning Wizard, dar fara succes.

M-am gandit si la ideea de a crea coloana Sold in tabela A_lstFisaCont, sa ordonez inregistrarile asa cum am eu nevoie (Cont, DataInr, NDS) si sa parcurg aceasta tabela cu un cursor facand update pe coloana Sold inregistrare curenta = Sold din inregistrarea de deasupra + debit - credit. Din cate am inteles, insa, cursorul este foarte lent si nu ma ajuta.

Ce solutii posibile am la problema mea?


Multumesc anticipat,
Ovidiu

Ceea ce ai tu este o problemă clasică de running sum, care nu prea poate fi rezolvată eficient, frumos şi corect (adică folosind doar facilităţi documentate) în SQL Server. Trebuie să renunţi la una dintre "eficient", "frumos" sau "corect". La care renunţi?

Dacă renunţi la "eficient", atunci rezultă ceea ce ai (eventul poţi să faci un index compus, pe Cont, DataInr şi NDS, dar nu ajută la mare lucru).

Dacă renunţi la "frumos", atunci foloseşti un cursor ca să completezi coloana respectivă în tabelă. În acest caz (la zeci de mii de înregistrări), cursorul va merge mult mai repede decât query-ul actual.

Dacă renunţi la "corect", atunci foloseşti o facilitate nedocumentată şi completezi acea coloană printr-un UPDATE de genul @variabila=coloana=expresie, astfel:

USE tempdb

GO

CREATE TABLE A_lstFisaCont (

      cont varchar(20) not null,

      datainr datetime null,

      nds int not null,

      coduser int not null default (1),

      debit money not null,

      credit money not null,

      sold money null

)

 

GO

INSERT INTO A_lstFisaCont (cont, datainr, nds, debit, credit)

SELECT '456' as cont, NULL as datainr, 1 as nds, 200 as debit, 0 as credit

UNION ALL SELECT '1012' as cont, NULL as datainr, 1 as nds, 0 as debit, 200 as credit

UNION ALL SELECT '5121' as cont, '20080101' AS datainr, 2 as nds, 200 as debit, 0 as credit

UNION ALL SELECT '456' as cont, '20080101' AS datainr, 2 as nds, 0 as debit, 200 as credit

UNION ALL SELECT '5121' as cont, '20080102' AS datainr, 3 as nds, 10 as debit, 0 as credit

UNION ALL SELECT '766' as cont, '20080102' AS datainr, 3 as nds, 0 as debit, 10 as credit

UNION ALL SELECT '5121' as cont, '20080103' AS datainr, 4 as nds, 12 as debit, 0 as credit

UNION ALL SELECT '766' as cont, '20080103' AS datainr, 4 as nds, 0 as debit, 12 as credit

UNION ALL SELECT '5121' as cont, '20080103' AS datainr, 5 as nds, 13 as debit, 0 as credit

UNION ALL SELECT '766' as cont, '20080103' AS datainr, 5 as nds, 0 as debit, 13 as credit

UNION ALL SELECT '766' as cont, '20080131' AS datainr, 6 as nds, 35 as debit, 0 as credit

UNION ALL SELECT '121' as cont, '20080131' AS datainr, 6 as nds, 0 as debit, 35 as credit

 

GO

SELECT * FROM A_lstFisaCont ORDER BY cont, datainr, nds

 

GO

DECLARE @cont varchar(20), @sold money

UPDATE x SET

      @sold=sold=debit-credit+CASE WHEN @cont=cont THEN @sold ELSE 0 END,    

      @cont=cont

FROM (SELECT TOP 9223372036854775807 * FROM A_lstFisaCont ORDER BY cont, datainr, nds) x

 

GO

SELECT * FROM A_lstFisaCont ORDER BY cont, datainr, nds

 

GO

--DROP TABLE A_lstFisaCont

Totuşi, nu recomand această variantă, pentru că nu e documentat niciunde că UPDATE-ul se va face în ordinea specificată în ORDER BY-ul din subquery (e doar o conincidenţă). Drept urmare, recomand varianta cu cursor, cel puţin până când se va implementa în SQL Server funcţiile agregate în care se poate preciza ordinea. Pentru ca acest lucru să se întâmple cât mai curând, vă rog să votaţi sugestia următoare pe Connect (eventual şi celelalte sugestii menţionate în http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc):

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254387

Răzvan

PS. Clauza WHERE din query-ul tău putea fi scrisă mai simplu astfel:

where cont=fc.cont and coduser=1
and (datainr is null or fc.datainr>datainr or fc.datainr=datainr and fc.nds>=nds)

Am incercat varianta cu cursor, insa este de 4-5 ori mai lenta decat varianta cu select (asa cum era initial). Codul sursa pentru cursor este afisat mai jos:

--drop table A_tmplstFisaCont

select *, cast(0 as decimal(28,6)) as Sold into A_tmplstFisaCont from A_lstFisaCont order by cont, datainr, nds

update A_tmplstFisaCont set sold = 0

DECLARE @cont varchar(16), @datainr datetime, @nds int, @coduser int, @debit decimal(28,6), @credit decimal(28,6), @sold decimal(28,6)

DECLARE abc CURSOR FOR
SELECT cont, datainr, nds, coduser, debit, credit, sold
FROM A_tmplstFisaCont
--ORDER BY cont, datainr, nds  --daca pun order by, cursorul este read-only si nu pot face update pe el

OPEN abc

FETCH NEXT FROM abc
INTO @cont, @datainr, @nds, @coduser, @debit, @credit, @sold

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE A_tmplstFisaCont SET Sold = isnull((select top 1 sold from A_tmplstFisaCont where cont = @cont and coduser = 1),0) + debit - credit
  WHERE CURRENT OF abc
  
  FETCH NEXT FROM abc
  INTO @cont, @datainr, @nds, @coduser, @debit, @credit, @sold
END

CLOSE abc
DEALLOCATE abc
GO

(Sunt ceva greseli in cod in sensul ca nu-mi calculkeaza soldul, inca nu mi-am dat seama unde, dar nici nu am cautat din moment ce ma intereseaza in primul rand sa fac algoritmul sa ruleze mai rapid.)

Am incercat si varianta cu FAST_FORWARD, dar tot fara rezultat. Codul este si el foarte lent in comparatie cu SELECT in SELECT:

drop table A_tmplstFisaCont

select *, cast(0 as decimal(28,6)) as Sold into A_tmplstFisaCont from A_lstFisaCont order by cont, datainr, nds

alter table A_tmplstFisaCont add [iId] [int] IDENTITY (1, 1) NOT NULL

go

CREATE CLUSTERED
INDEX [iIdIdx] ON [dbo].[A_tmplstFisaCont] ([iId])
ON [PRIMARY]

GO

update A_tmplstFisaCont set sold = 0

DECLARE @id int, @cont varchar(16), @datainr datetime, @nds int, @coduser int, @debit decimal(28,6), @credit decimal(28,6), @sold decimal(28,6)

DECLARE abc CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT iid, cont, datainr, nds, coduser, debit, credit, sold
FROM A_tmplstFisaCont
ORDER BY iid

OPEN abc

FETCH NEXT FROM abc
INTO @id, @cont, @datainr, @nds, @coduser, @debit, @credit, @sold

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE A_tmplstFisaCont SET Sold = isnull((select top 1 sold from A_tmplstFisaCont where cont = @cont and coduser = 1),0) + debit - credit
  where iid=@id
--  WHERE CURRENT OF abc
  
  FETCH NEXT FROM abc
  INTO @id, @cont, @datainr, @nds, @coduser, @debit, @credit, @sold
END

CLOSE abc
DEALLOCATE abc
GO

PS: Am inlocuit money cu decimal(28,6) pentru ca asa apare in BD mea.

Alte idei ar mai fi?

o functie cu parametru @data

select coalesce(A.cont,B.cont) cont ,
           coalesce(A.soldInit,0) as soldInit,
            coalesce(B.Debit,0) debit,
           coalesce(B.Credit,0) credit
from
 ( ----------sold initial
   select coalesce(x.cont,y.cont) as cont, coalesce(x.debit,0)-coalesce(y.credit,0) as ssoldinit
      from
        ( select sum(debit) as debit , cont ....
        )X
        full join
       ( select sum(credit) as credit, cont ....
        )y
        on x.cont=y.cont
  )A--sold initial

full join

(  ----sold curent
      select coalesce(x.cont,y.cont) as cont, coalesce(x.debit,0) as debit ,coalesce(y.credit,0) as credit
      from
        ( select sum(debit) as debit , cont ....
        )X
        full join
       ( select sum(credit) as credit, cont ....
        )y
        on x.cont=y.cont
)B --sold curent

on A.cont=B.cont

ovello:

Am urmatorul SQL:


select  left(fc.cont,1) as clasa,left(fc.cont,2) as subcls,
    isnull((
   
    SELECT sum(debit-credit) as sold
    FROM A_lstFisaCont
    where cont=fc.cont and
    ((datainr is not null and (fc.datainr>datainr or (fc.datainr=datainr and fc.nds>=nds))) or datainr is null) and
    coduser=1

    ),0) as sold, fc.debit-fc.credit-fc.sid+fc.sic as sSold,fc.debit-fc.sid as sdebit,fc.credit-fc.sic as scredit

from A_lstFisaCont FC
where fc.coduser=1
order by fc.cont,fc.datainr,fc.NDS


Daca tabela A_lstFisaContcontine 50.000 de inregistrari, SQL-ul returneaza inregistrarile dupa aproximativ 4 minute (sau mai mult, in functie de cat de incarcat mi-e procesorul de la celelalte aplicatii deschise), ceea ce e foarte mult. Am calculator performant, nu asta este important; important este ca trebuie sa fac ca rezultatul generat sa iasa mai repede, fie prin rescrierea SQL-ului, fie prin regandirea algoritmului de calcul al campului Sold (aici e problema). Am incercat sa pun indecsi pe tabela, am folosit inclusiv Index Tuning Wizard, dar fara succes.

M-am gandit si la ideea de a crea coloana Sold in tabela A_lstFisaCont, sa ordonez inregistrarile asa cum am eu nevoie (Cont, DataInr, NDS) si sa parcurg aceasta tabela cu un cursor facand update pe coloana Sold inregistrare curenta = Sold din inregistrarea de deasupra + debit - credit. Din cate am inteles, insa, cursorul este foarte lent si nu ma ajuta.

Ce solutii posibile am la problema mea?


Multumesc anticipat,
Ovidiu

var datacontext=new SomedataContext()
var query=from cont in datacontext.FIsacont
let sold=(mdatacontext.FisaCont.where(x=>x.cont==cont.cont && Sqlmethods.DateDiff(x.DataInnr,cont.Datanr) ...&& x.coduser==1).sum(x=>x.debit-x.Credit)
where fc.coduser==1
order by cont.cont,cont.datainr,cont.NDS
select new {clasa=cont.cont(subbstring(0,1),subclasa=cont.cont(subbstring(0,2),sold??0,ssold=cont.debit-cont-credit...}

foreach(var item in query)
Console.writeln(item)
Nu emai eleganta soltia asta :))?

---

Secolul XXI ori va fi religios ori nu va fi deloc

crestinul:

var datacontext=new SomedataContext() var query=from cont in datacontext.FIsacont let sold=(mdatacontext.FisaCont.where(x=>x.cont==cont.cont && Sqlmethods.DateDiff(x.DataInnr,cont.Datanr) ...&& x.coduser==1).sum(x=>x.debit-x.Credit) where fc.coduser==1 order by cont.cont,cont.datainr,cont.NDS select new {clasa=cont.cont(subbstring(0,1),subclasa=cont.cont(subbstring(0,2),sold??0,ssold=cont.debit-cont-credit...} foreach(var item in query) Console.writeln(item)

Bine ar fi sa si pricep ceva din chestia asta. Aplicatia unde am probleme este implementata in C# (.NET Framework 2.0), insa din cate am inteles este mai rapid sa ma folosesc numai de SQL cand fac astfel de operatii complexe pe tabele. Daca gresesc, va rog sa ma corectati.

Asta e cod LInq2Sql oricum merge pe dot net 3.5

---

Secolul XXI ori va fi religios ori nu va fi deloc

Am finisat varianta cu cursor. Am incercat sa scap de selectul ala inutil care cred ca imi diminua performanta si a iesit asta:

drop table A_tmplstFisaCont

select *, cast(0 as decimal(28,6)) as Sold into A_tmplstFisaCont from A_lstFisaCont order by cont, datainr, nds

alter table A_tmplstFisaCont add [iId] [int] IDENTITY (1, 1) NOT NULL

go

CREATE CLUSTERED
INDEX [iIdIdx] ON [dbo].[A_tmplstFisaCont] ([iId])
ON [PRIMARY]

GO

update A_tmplstFisaCont set sold = 0

DECLARE @id int, @cont varchar(16), @datainr datetime, @nds int, @coduser int, @debit decimal(28,6), @credit decimal(28,6), @sold decimal(28,6)
declare @bFirstRow int, @dPrevSold decimal(28,6), @sPrevCont varchar(16)

set @bFirstRow = 1
set @dPrevSold = 0
set @sPrevCont = ''

DECLARE abc CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT iid, cont, datainr, nds, coduser, debit, credit, sold
FROM A_tmplstFisaCont
ORDER BY iid

OPEN abc

FETCH NEXT FROM abc
INTO @id, @cont, @datainr, @nds, @coduser, @debit, @credit, @sold

WHILE @@FETCH_STATUS = 0
BEGIN
  if @cont = @sPrevCont
begin
set @dPrevSold = 0
set @sPrevCont <> @cont
  end
  if @bFirstRow = 1
begin
      set @Sold = @debit - @credit
set @bFirstRow = 0
end
else
begin
      set @Sold = @dPrevSold + @debit - @credit
end
    UPDATE A_tmplstFisaCont SET Sold = @sold
    where iid=@id
set @dPrevSold = @sold

  FETCH NEXT FROM abc
  INTO @id, @cont, @datainr, @nds, @coduser, @debit, @credit, @sold
END

CLOSE abc
DEALLOCATE abc
GO

De aceasta data codul este de 5 ori mai rapid decat varianta cu select (varianta initiala).

cursor mai rapid ca dataseturi?hm

---

Secolul XXI ori va fi religios ori nu va fi deloc

Cursor mai rapid decat select-ul initial (vezi primul post).

In final am gasit o solutie si mai buna. Modific selectul initial a.i. sa scap de selectul interior. In loc de selectul interior, coloana sold va avea valoarea 0 pe toate inregistrarile. Adaug o coloana noua la DT care se numeste "CalculatedSum". Setul de date astfel obtinut il incarc intr-un DataTable. Parcurg DT cu ajutorul urmatoarei functii (C#) si calculez sold:

if (dtCopy != null)
{
bool bFirstRow = true;
decimal dPrevSold = 0;
string sPrevCont = "";
foreach (DataRow dr in dtCopy.Rows)
{
if (!dr["CONT"].ToString().Equals(sPrevCont))
{
dPrevSold = 0;
sPrevCont = dr["CONT"].ToString();
}
if (bFirstRow)
{
dr["CalculatedSum"] = Convert.ToDecimal(dr["debit"]) - Convert.ToDecimal(dr["credit"]);
bFirstRow = false;
}
else
{
dr["CalculatedSum"] = dPrevSold + Convert.ToDecimal(dr["debit"]) - Convert.ToDecimal(dr["credit"]);
}
dPrevSold = Convert.ToDecimal(dr["CalculatedSum"]);
}
}

Dupa modificarea de mai sus toata treaba tine 10 secunde in loc de 4-5 minute cat tinea initial. (Cred ca se poate scapa de coloana noua, numai ca eu aveam DT asociat unui grid - caz special si facea figuri - evident, ca solutia se mai poate finisa)

Consider problema rezolvata si multumesc tuturor celor care s-au implicat si au incercat sa ma ajute :)