UGISS Community

 Ciao,

ho il seguente problema:

ho una stored procedure che calcola la classificazione ABC di una selezione di una tabella. Sono "obbligato" ad implementare la cosa con una stored procedure dato che l'implementazione utilizza tabelle temporanee ed una di questa necessita di una INSERT INTO per popolare un campo IDENTITY necessario ad una successiva query non-equal join che effettua il calcolo del progressivo necessario alla classificazione

fin qui tutto ok

ho creato una STPcalcoloABC che prende come parametri il livello di classe A, il livello di classe B ed un parametro che mi serve a limitare temporalmente l'analisi

quindi una chiamata del tipo

exec STPcalcoloABC 0.8, 0.15, 36

torna un risultato corretto

il problema sorge quando tento di "inglobare" questa chiamata in un funzione valutata a livello di tabella, infatti:

1. non posso fare una insert exec in una variabile tabella dato che la cosa genera "effetti collaterali"

2. non posso implementare tutto nella funzione dato che non sarebbe possibile la "insert into" di cui parlavo prima, a sua volta necessaria per popolare un campo ID che accolga un progressivo di riga diverso per ogni riga (cosa attualmente ottenuta appunto con una insert into che permette l'utilizzo della funzione IDENTITY)

3. non vorrei utilizzare una tabella reale sul db (tipo TEMP_QUALCOSA) perchè non mi piace (che ci volete fare, sono fatto così)

4. non voglio usare i cursori (il cui uso mi permetterebbe di eliminare la funzione IDENTITY e fare tutto in una tabella temporanea in memoria e quindi all'interno di una funzione) o soluzioni bislacche dalle prestazioni anche peggiori (si... le ho provate)

quindi la domanda:

esistono soluzioni o mi rassegno alla stored proc?

Ciao

Mauro

 

Et voila:

dalla stored procedure

 USE [DITTA]
GO
/****** Oggetto:  StoredProcedure [dbo].[STPStatClassificazioneABCPesoVenduto]    Data script: 04/17/2008 11:49:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Mauro Branca>
-- Create date: <20080417>
-- Description:    <classificazione ABC degli articoli in base>
--              <al peso venduto negli ultimi n mesi>
-- =============================================
CREATE PROCEDURE [dbo].[STPStatClassificazioneABCPesoVenduto]
    @CLASSEA DECIMAL(18, 8) = 0.8,
    @CLASSEB DECIMAL(18, 8) = 0.15,
    @NUMEROMESI INTEGER = 37
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @PESORIF FLOAT;

    SELECT
        BV.ARTICOLO, CAST(SUM(BV.PesoQTAVenduta) AS INTEGER) AS PESOTOT
    INTO #T
    FROM
        VSTATBOLLEVENDITA BV
    WHERE
        BV.DATAPROT >= CONVERT(CHAR(8), DATEADD(MM, -@NUMEROMESI, GETDATE()), 112)
    GROUP BY
        BV.ARTICOLO


    SELECT @PESORIF = SUM(PESOTOT) FROM #T

    SELECT
        IDENTITY(INT, 1, 1) AS ID, #T.ARTICOLO, #T.PESOTOT, CAST(#T.PESOTOT / @PESORIF AS DECIMAL(18, 8)) AS PESOPERC
    INTO
        #T1
    FROM
        #T
    ORDER BY
        #T.PESOTOT DESC

    SELECT
        ID, ARTICOLO, PESOTOT, PESOPERC, PERCABC,
        CASE
            WHEN PERCABC <= @CLASSEA THEN 'A'
            WHEN PERCABC > @CLASSEA AND PERCABC <= @CLASSEA + @CLASSEB THEN 'B'
            ELSE 'C'
        END AS ABC
    FROM
    (
        SELECT
            TA.ID, TA.ARTICOLO, TA.PESOTOT, TA.PESOPERC, SUM(TB.PESOPERC) AS PERCABC
        FROM
            #T1 AS TA
            INNER JOIN #T1 AS TB
                ON TA.ID >= TB.ID
        GROUP BY
            TA.ID, TA.ARTICOLO, TA.PESOTOT, TA.PESOPERC
    ) AS T2


    DROP TABLE #T
    DROP TABLE #T1

END

sono passato alla funzione valutata a livello di tabella

 USE [DITTA]
GO
/****** Oggetto:  UserDefinedFunction [dbo].[fStatClassificazioneABCPesoVenduto]    Data script: 04/17/2008 11:50:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Mauro Branca>
-- Create date: <20080417>
-- Description:    <classificazione ABC degli articoli in base>
--              <al peso venduto negli ultimi n mesi>
-- =============================================
CREATE FUNCTION [dbo].[fStatClassificazioneABCPesoVenduto]
(    
    @CLASSEA DECIMAL(18, 8) = 0.8,
    @CLASSEB DECIMAL(18, 8) = 0.15,
    @NUMEROMESI INTEGER = 37
)
RETURNS @T TABLE
(
    ID INTEGER,
    ARTICOLO CHAR(30),
    PESOTOT INTEGER,
    PESOPERC DECIMAL(18, 8),
    PERCABC DECIMAL(18, 8),
    ABC CHAR
)
AS
BEGIN

DECLARE @PESORIF AS DECIMAL(18, 8)
DECLARE @ID AS INTEGER

INSERT INTO @T(ID, ARTICOLO, PESOTOT, PESOPERC, PERCABC, ABC)
SELECT
    0 AS ID, BV.ARTICOLO, CAST(SUM(BV.PesoQTAVenduta) AS INTEGER) AS PESOTOT, CAST(0 AS DECIMAL(18, 8)) AS PESOPERC, CAST(0 AS DECIMAL(18, 8)) AS PERCABC, ' ' AS ABC
FROM
    VSTATBOLLEVENDITA BV
WHERE
    BV.DATAPROT >= CONVERT(CHAR(8), DATEADD(MM, -@NUMEROMESI, GETDATE()), 112)
GROUP BY
    BV.ARTICOLO


SELECT @PESORIF = SUM(PESOTOT) FROM @T


SET @ID = 1
WHILE EXISTS(SELECT TOP 1 * FROM @T WHERE ID = 0) BEGIN
  UPDATE
        @T
    SET
        ID = @ID,
        PESOPERC = CAST(PESOTOT AS DECIMAL(18, 8)) / @PESORIF--) AS DECIMAL(18, 8))
    WHERE
        ARTICOLO = (SELECT TOP 1 ARTICOLO FROM @T WHERE ID = 0 ORDER BY PESOTOT DESC)
    
    SET @ID = @ID + 1
END

UPDATE
    @T
SET
    PERCABC = T2.PERCABC,
    ABC =
        CASE
            WHEN T2.PERCABC <= @CLASSEA THEN 'A'
            WHEN T2.PERCABC <= @CLASSEA + @CLASSEB THEN 'B'
            ELSE 'C'
        END
FROM
    @T T
    INNER JOIN
    (
        SELECT
            TA.ID, TA.ARTICOLO, TA.PESOTOT, TA.PESOPERC, SUM(TB.PESOPERC) AS PERCABC
        FROM
            @T AS TA
            INNER JOIN @T AS TB
                ON TA.ID >= TB.ID
        GROUP BY
            TA.ID, TA.ARTICOLO, TA.PESOTOT, TA.PESOPERC
    ) AS T2
        ON T.ID = T2.ID

RETURN
END

 
sia pur con un (piccolo) aggravio nelle prestazioni, dovuto alla sostituzione dell'uso della funzione IDENTITY con un ciclo while di calcolo del progressivo di riga. 

quindi la domanda diventa:

come si può fare di meglio (in termini di prestazioni)?

ciao

Mauro 

Puoi postare uno script di esempio che utilizzi la tua funzione, in modo che sia possibile replicare la tua situazione anche sulla mia macchina, cosi da poter vedere come riscrivere la funzione senza far uso del ciclo while?

Vero, dovevo pensarci subito ... quindi provvedo:

/****** Oggetto:  Table [dbo].[VSTATBOLLEVENDITA]    Data script: 04/21/2008 08:47:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VSTATBOLLEVENDITA](
    [ARTICOLO] [char](30) NULL,
    [PESOQTAVENDUTA] [decimal](18, 8) NULL,
    [DATAPROT] [char](8) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART01', 100000, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART02', 90000, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART03', 88000, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART04', 80000, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART05', 60000, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART06', 20000, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART07', 8000, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART08', 6000, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART09', 3000, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART10', 1000, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART11', 500, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART12', 200, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART13', 100, '20080103')

INSERT INTO VSTATBOLLEVENDITA(ARTICOLO, PESOQTAVENDUTA, DATAPROT)
VALUES ('ART14', 50, '20080103')

GO
/****** Oggetto:  StoredProcedure [dbo].[STPStatClassificazioneABCPesoVenduto]    Data script: 04/17/2008 11:49:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Mauro Branca>
-- Create date: <20080417>
-- Description:    <classificazione ABC degli articoli in base>
--              <al peso venduto negli ultimi n mesi>
-- =============================================
CREATE PROCEDURE [dbo].[STPStatClassificazioneABCPesoVenduto]
    @CLASSEA DECIMAL(18, 8) = 0.8,
    @CLASSEB DECIMAL(18, 8) = 0.15,
    @NUMEROMESI INTEGER = 37
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @PESORIF FLOAT;

    SELECT
        BV.ARTICOLO, CAST(SUM(BV.PesoQTAVenduta) AS INTEGER) AS PESOTOT
    INTO #T
    FROM
        VSTATBOLLEVENDITA BV
    WHERE
        BV.DATAPROT >= CONVERT(CHAR(8), DATEADD(MM, -@NUMEROMESI, GETDATE()), 112)
    GROUP BY
        BV.ARTICOLO


    SELECT @PESORIF = SUM(PESOTOT) FROM #T

    SELECT
        IDENTITY(INT, 1, 1) AS ID, #T.ARTICOLO, #T.PESOTOT, CAST(#T.PESOTOT / @PESORIF AS DECIMAL(18, 8)) AS PESOPERC
    INTO
        #T1
    FROM
        #T
    ORDER BY
        #T.PESOTOT DESC

    SELECT
        ID, ARTICOLO, PESOTOT, PESOPERC, PERCABC,
        CASE
            WHEN PERCABC <= @CLASSEA THEN 'A'
            WHEN PERCABC > @CLASSEA AND PERCABC <= @CLASSEA + @CLASSEB THEN 'B'
            ELSE 'C'
        END AS ABC
    FROM
    (
        SELECT
            TA.ID, TA.ARTICOLO, TA.PESOTOT, TA.PESOPERC, SUM(TB.PESOPERC) AS PERCABC
        FROM
            #T1 AS TA
            INNER JOIN #T1 AS TB
                ON TA.ID >= TB.ID
        GROUP BY
            TA.ID, TA.ARTICOLO, TA.PESOTOT, TA.PESOPERC
    ) AS T2


    DROP TABLE #T
    DROP TABLE #T1

END

GO

GO
/****** Oggetto:  UserDefinedFunction [dbo].[fStatClassificazioneABCPesoVenduto]    Data script: 04/17/2008 11:50:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Mauro Branca>
-- Create date: <20080417>
-- Description:    <classificazione ABC degli articoli in base>
--              <al peso venduto negli ultimi n mesi>
-- =============================================
CREATE FUNCTION [dbo].[fStatClassificazioneABCPesoVenduto]
(   
    @CLASSEA DECIMAL(18, 8) = 0.8,
    @CLASSEB DECIMAL(18, 8) = 0.15,
    @NUMEROMESI INTEGER = 37
)
RETURNS @T TABLE
(
    ID INTEGER,
    ARTICOLO CHAR(30),
    PESOTOT INTEGER,
    PESOPERC DECIMAL(18, 8),
    PERCABC DECIMAL(18, 8),
    ABC CHAR
)
AS
BEGIN

DECLARE @PESORIF AS DECIMAL(18, 8)
DECLARE @ID AS INTEGER

INSERT INTO @T(ID, ARTICOLO, PESOTOT, PESOPERC, PERCABC, ABC)
SELECT
    0 AS ID, BV.ARTICOLO, CAST(SUM(BV.PesoQTAVenduta) AS INTEGER) AS PESOTOT, CAST(0 AS DECIMAL(18, 8)) AS PESOPERC, CAST(0 AS DECIMAL(18, 8)) AS PERCABC, ' ' AS ABC
FROM
    VSTATBOLLEVENDITA BV
WHERE
    BV.DATAPROT >= CONVERT(CHAR(8), DATEADD(MM, -@NUMEROMESI, GETDATE()), 112)
GROUP BY
    BV.ARTICOLO


SELECT @PESORIF = SUM(PESOTOT) FROM @T


SET @ID = 1
WHILE EXISTS(SELECT TOP 1 * FROM @T WHERE ID = 0) BEGIN
  UPDATE
        @T
    SET
        ID = @ID,
        PESOPERC = CAST(PESOTOT AS DECIMAL(18, 8)) / @PESORIF--) AS DECIMAL(18, 8))
    WHERE
        ARTICOLO = (SELECT TOP 1 ARTICOLO FROM @T WHERE ID = 0 ORDER BY PESOTOT DESC)
   
    SET @ID = @ID + 1
END

UPDATE
    @T
SET
    PERCABC = T2.PERCABC,
    ABC =
        CASE
            WHEN T2.PERCABC <= @CLASSEA THEN 'A'
            WHEN T2.PERCABC <= @CLASSEA + @CLASSEB THEN 'B'
            ELSE 'C'
        END
FROM
    @T T
    INNER JOIN
    (
        SELECT
            TA.ID, TA.ARTICOLO, TA.PESOTOT, TA.PESOPERC, SUM(TB.PESOPERC) AS PERCABC
        FROM
            @T AS TA
            INNER JOIN @T AS TB
                ON TA.ID >= TB.ID
        GROUP BY
            TA.ID, TA.ARTICOLO, TA.PESOTOT, TA.PESOPERC
    ) AS T2
        ON T.ID = T2.ID

RETURN
END

GO

-- caso 1
EXEC STPStatClassificazioneABCPesoVenduto

-- caso 2
SELECT * FROM fstatclassificazioneABCPesoVenduto(default, default, default)

Note: 1. non ho stabilito un database (uno qualunque dei vostri temporanei dovrebbe andare bene)

2: la tabella vstatbollevendita non ha chiavi e/o indici (nel caso reale si tratta di una vista)

Mauro 

Ciao Mauro

ecco qui una soluzione che non fa uso di cicli while:

CREATE FUNCTION [dbo].[fStatClassificazioneABCPesoVenduto2]
(   
    @CLASSEA DECIMAL(18, 8) = 0.8,
    @CLASSEB DECIMAL(18, 8) = 0.15,
    @NUMEROMESI INTEGER = 37
)
RETURNS table as
return
(
with    cte
          as ( SELECT   BV.ARTICOLO,
                        CAST(SUM(BV.PesoQTAVenduta) AS INTEGER) AS PESOTOT
               FROM     VSTATBOLLEVENDITA BV
               WHERE    BV.DATAPROT >= CONVERT(CHAR(8), DATEADD(MM, -@NUMEROMESI, GETDATE()), 112)
               GROUP BY BV.ARTICOLO
             ) ,
        cte2
          as ( select   pesorif = sum(pesotot)
               from     cte
             ) ,
        cte3
          as ( select   id = row_number() over ( order by articolo ),
                        *,
                        CAST(PESOTOT AS DECIMAL(18, 8))
                        / cast(PESORIF AS DECIMAL(18, 8)) AS PESOPERC
               from     cte
                        cross join [cte2]
             ) ,
        cte4
          as ( SELECT   TA.ID,
                        TA.ARTICOLO,
                        TA.PESOTOT,
                        TA.PESOPERC,
                        SUM(TB.PESOPERC) AS PERCABC
               FROM     cte3 AS TA
                        INNER JOIN cte3 AS TB ON TA.ID >= TB.ID
               GROUP BY TA.ID,
                        TA.ARTICOLO,
                        TA.PESOTOT,
                        TA.PESOPERC
             )
    SELECT  ID,
            ARTICOLO,
            PESOTOT,
            PESOPERC,
            PERCABC,
            CASE WHEN PERCABC <= @CLASSEA THEN 'A'
                 WHEN PERCABC > @CLASSEA
                      AND PERCABC <= @CLASSEA + @CLASSEB THEN 'B'
                 ELSE 'C'
            END AS ABC
    from    cte4
)
go

Una nota: la funzione deve calcolare il running totale sulla colonna pesoperc, ed in questo casi SQL Server non eccelle, in quanto il problema può essere risolto solamente con un'accesso row-by-row.

Quindi:

1. se hai poche righe (<1000) probabilmente le perfomance della soluzione suddetta saranno cmq buone
2. se hai molto righe probabilmente ti conviene valutare la scrittura della parte che calcola il running totale come codice managed (vedi questo post di Andrea: http://community.ugiss.org/blogs/abenedetti/archive/2007/10/19/running-totals-sqlclr-version.aspx )
3. se non vuoi usare SQLCLR puoi provare ad implementare la soluzione descritta da Mercello tramite CROSS-APPLY e UDF: http://blogs.dotnethell.it/epomops/Calcolo-progressivo-via-Cte-e-operatore-Apply__12655.aspx

Buon lavoro