Code Domain

1 - Also C_1 .. C_12 sind die Spalten der Tabelle und du möchtest über Parameter gesteuert sagen "summiere Spalte 1 bis 3" oder "summiere Spalte 5 bis 7" usw. je Produkt, richtig?

2 - Hallo Sebastian,

jo 1..n wobei n variabel!

Gruß JJR
P.S.: Hat alles gefunzt?!

3 - Hallo Jörg,

ich glaube das geht (zumindest in TSQL) nur mit dynamischem SQL - was anderes würde mir da nicht einfallen.

Hab mal was ausprobiert:

ALTER PROCEDURE [dbo].[SpEarningsForProduct]
@Product varchar(50),
@StartMonth int,
@EndMonth int
AS
BEGIN
DECLARE @VDefinition nvarchar(500);
DECLARE @Stmt nvarchar(500);
DECLARE @Counter int;

SET @VDefinition = N'@VProduct varchar(50)';
SET @Stmt = 'select product, sum(';
SET @Counter = @StartMonth;
WHILE @Counter <= @EndMonth
BEGIN
SET @Stmt = @Stmt + 'C_';
SET @Stmt = @Stmt + cast(@Counter as nvarchar);
IF @Counter < @EndMonth
BEGIN
SET @Stmt = @Stmt + '+';
END
SET @Counter = @Counter + 1;
END
SET @Stmt = @Stmt + ') from turnover where product = @VProduct group by product';

exec sp_executesql @Stmt, @VDefinition, @VProduct = @Product;
END

Die Tabelle dazu ist:

CREATE TABLE [dbo].[TURNOVER](
[PRODUCT] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[C_1] [float] NULL,
[C_2] [float] NULL,
[C_3] [float] NULL,
[C_4] [float] NULL,
[C_5] [float] NULL,
[C_6] [float] NULL,
[C_7] [float] NULL,
[C_8] [float] NULL,
[C_9] [float] NULL,
[C_10] [float] NULL,
[C_11] [float] NULL,
[C_12] [float] NULL
) ON [PRIMARY]

Das sollte zumindestens etwas performanter als der Funktionsaufruf für jede Spalte sein - auch wenn es sicherlich noch besser geht.

Funktioniert natürlich nur wenn die Spalten auch so schön fortlaufend benannt sind - wenn die jetzt aber Jan-Feb-Mar usw. heißen...



Grüße,
Sebastian

P.S.: hat alles wunderbar funktioniert

4 - Hallo Sebastian,

versuche das mal auszuprobieren und berichte was schneller ist

Gruß JJR
P.S.: Auf einer Datenbank, wo ein bisserl was drinne ist

5 - Hallo Sebastian,

von der Performance bei 2000 Produkten ist mir nix aufgefallen nur das ich meine Funktion wie folgt änder sollte ->
CREATE FUNCTION SF_CHECK_REVENUE_IN_MONTH
(
@Revenue float,
@RevenueMonth int,
@RevenueLastMonth int
)
RETURNS float
AS
BEGIN
RETURN case when @Revenue is not null and @RevenueMonth <= @RevenueLastMonth then @Revenue else 0.0 end
END

Weil sonst die NULL - Einträge ein wenig Ärger bereiten
Gruß JJR

6 - Hallo Jörg,

offenbar sind 2.000 Sätze noch zu wenig um da einen Unterschied zu merken - hatte mich schon gewundert und habe es deswegen mal mit einer Tabelle mit 100.000 Datensätzen ausprobiert (so viele Produkte sollte ein ordentlicher Konzern schon haben ).

Da du anscheinend die Funktion für alle Artikel anwenden willst vereinfacht sie sich natürlich:

ALTER PROCEDURE [dbo].[SpEarningsForAllProducts]
-- Add the parameters for the stored procedure here
@StartMonth int,
@EndMonth int
AS
BEGIN
DECLARE @Stmt nvarchar(500);
DECLARE @Counter int;

SET @Stmt = 'select product, sum(';
SET @Counter = @StartMonth;
WHILE @Counter <= @EndMonth
BEGIN
SET @Stmt = @Stmt + 'IsNull(C_';
SET @Stmt = @Stmt + cast(@Counter as nvarchar);
SET @Stmt = @Stmt + ', 0.0)';
IF @Counter < @EndMonth
BEGIN
SET @Stmt = @Stmt + '+';
END
SET @Counter = @Counter + 1;
END
SET @Stmt = @Stmt + ') from turnover group by product';

exec sp_executesql @Stmt;
END

Und nun der Vergleich:

-- Lösung 1
select sum(dbo.SF_CHECK_REVENUE_IN_MONTH(C_1,1,3)+
dbo.SF_CHECK_REVENUE_IN_MONTH(C_2,2,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_3,3,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_4,4,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_5,5,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_6,6,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_7,7,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_8,8,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_9,9,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_10,10,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_11,11,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_12,12,3)), product
from turnover group by product;

--> 4 Sek.

-- Lösung 2
exec SpEarningsForAllProducts 1, 3;

--> 1 Sek.


75% schneller kann sich schon sehen lassen.

Und sieht dabei noch eleganter aus

Grüße,
Sebastian

P.S.: mit den NULL-Werten ist natürlich richtig, müssen abgefangen werden

7 - Hallo Sebastian,

ja ich gebe Dir recht der Aufruf sieht eleganter aus. Ob dies hat jedoch m.E. einen negativen Effekt auf die Wartbarkeit. Angenommen es gibt noch eine Spalte Year und man möchte darüber grouppieren, dann muß ich das dynamic sql anpassen. Wenn ich es in einer View mit einem Join benutzen möchte auch... von daher sprechen jetzt 75% mehr performance gegen evtl. Mehraufwand bei CR's... Was meinst Du? Gruß JJR