locked
Best solution for compounded update? RRS feed

  • Question

  • In example below I want to increase all salaries in *PayRoll* by the percentage found in *Increase*, starting from Increase Date in *Increase* and going forward.

    This logic works fine if you have a single increase, but in my example of the 3 increases it will obviously only apply the last one instead of compounding (i.e. will not apply 1st increase, then apply 2nd increase on top of 1st and 3rd on top of 2nd). I understand exactly why set-based command will not work but want to know best solution to problem (currently can only provide RBAR solution with cursor or loop). I look forward to your suggestions...

        IF OBJECT_ID('PayRoll') IS NOT NULL
          DROP TABLE PayRoll
        GO
        
        CREATE TABLE [dbo].[PayRoll]
          (
             [PayRollID]    [INT] IDENTITY(1, 1) NOT NULL,
             [EmployeeNo]   [INT] NOT NULL,
             [EmployeeName] [VARCHAR](8) NOT NULL,
             [Month]        [DATE] NOT NULL,
             [Salary]       [MONEY] NOT NULL,
             CONSTRAINT [PK_PayRoll] PRIMARY KEY CLUSTERED ( [PayRollID] ASC )
          )
        GO 
        
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-01-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-02-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-03-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-04-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-05-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-06-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-07-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-08-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-09-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-10-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-11-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-12-01' AS Date), 5000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-01-01' AS Date), 6000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-02-01' AS Date), 6000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-03-01' AS Date), 6000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-04-01' AS Date), 6000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-05-01' AS Date), 6000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-06-01' AS Date), 6000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-07-01' AS Date), 6000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-08-01' AS Date), 6000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-09-01' AS Date), 6000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-10-01' AS Date), 6000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-11-01' AS Date), 6000)
        INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-12-01' AS Date), 6000)
        GO
        
        IF OBJECT_ID('Increase') IS NOT NULL
          DROP TABLE Increase
        GO
        
        CREATE TABLE [dbo].[Increase]
          (
             [IncreaseID]      [INT] IDENTITY(1, 1) NOT NULL,
             [IncreaseDate]    [DATE] NOT NULL,
             [IncreasePercent] [MONEY] NOT NULL,
             CONSTRAINT [PK_Increase] PRIMARY KEY CLUSTERED ( [IncreaseID] ASC )
          )
        GO 
        
        INSERT [dbo].[Increase] ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-04-01' AS Date), 5.0000)
        INSERT [dbo].[Increase] ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-09-01' AS Date), 10.0000)
        INSERT [dbo].[Increase] ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-11-01' AS Date), 7.0000)
        GO
        
        SELECT 'Before Update' AS [Description],
               *
        FROM   [dbo].[PayRoll]
        ORDER  BY [EmployeeNo],
                  [Month]
        
        UPDATE p
        SET    p.[Salary] = p.[Salary] * ( 1 + ( i.IncreasePercent / 100 ) )
        FROM   [dbo].[PayRoll] p
               INNER JOIN [dbo].[Increase] i
                       ON i.IncreaseDate <= p.[Month]
        
        SELECT 'Increases Expected' AS [Description],
               *
        FROM   [dbo].[Increase]
        ORDER  BY [IncreaseDate]
        
        SELECT 'After Update' AS [Description],
               *
        FROM   [dbo].[PayRoll]
        ORDER  BY [EmployeeNo],
                  [Month] 

    Wednesday, December 30, 2015 1:24 PM

Answers

  • Hi ,

    I am getting different result. I used SQCLR User-Defined Aggregate Function.
    It give great performance :-)
    Unfortunately, this is not Windows function so I use sub-query

    * I will post the whole SQLCLR code in several minute in a blog. 

    But I am getting different result from Eric (I think that My result correct if I did not done any simple math mistake).

    This is my solution using my function:

    SELECT 
    	o.PayRollID, o.EmployeeNo, o.EmployeeName, o.[Month], o.Salary
    	, finalSalary =  o.Salary * ((  (SELECT dbo.ArielyPercentageAgg([IncreasePercent]) FROM [Increase] i where i.IncreaseDate <= o.[Month]) + 100 ) / 100 )
    	,increasePercentFromBase = (SELECT dbo.ArielyPercentageAgg([IncreasePercent]) FROM [Increase] i where i.IncreaseDate <= o.[Month])  
    FROM PayRoll o
    GO

    Here is the screenshot of the result:


    * The function rounds the result to 2 chars after the zero. This can be change in the CREATE AGGREGATE easily if needed, by replacing the decimal in the input and in the return into decimal(32,10) for example.

    Happy New Year to everyone :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]




    • Edited by pituachMVP Friday, January 1, 2016 1:26 AM
    • Marked as answer by Eric__Zhang Friday, January 8, 2016 6:13 AM
    Thursday, December 31, 2015 6:25 PM

All replies

  • First of all: Thanks for a concise and complete example.

    Just some comments: Use table variables when possible for an example. Don't use reserved words like Month. You don't need to CAST the date literals. Using 'yyyymmdd' is an unambiguous format and is casted implicitly. And I don't like to store formatting in the data, doing so by using whole percentages is imho a bad idea, cause it makes math more complex.

    One problem is your approach. Changing an payroll entry is imho semantically wrong. Either it is paid like that or not. When not, then there should be no entry.

    You can easily use a set-based solution, when you have a base salary:

    DECLARE @PayRoll TABLE
        (
          PayRollID INT IDENTITY(1, 1)
                        NOT NULL ,
          EmployeeNo INT NOT NULL ,
          EmployeeName VARCHAR(8) NOT NULL ,
          [Month] DATE NOT NULL ,
          Salary MONEY NOT NULL ,
          PRIMARY KEY ( PayRollID ASC )
        );
        
    INSERT  @PayRoll
            ( EmployeeNo, EmployeeName, [Month], Salary )
    VALUES  ( 123, N'John Doe', '20160101', 5000 ),
            ( 123, N'John Doe', '20160201', 5000 ),
            ( 123, N'John Doe', '20160301', 5000 ),
            ( 123, N'John Doe', '20160401', 5000 ),
            ( 123, N'John Doe', '20160501', 5000 ),
            ( 123, N'John Doe', '20160601', 5000 ),
            ( 123, N'John Doe', '20160701', 5000 ),
            ( 123, N'John Doe', '20160801', 5000 ),
            ( 123, N'John Doe', '20160901', 5000 ),
            ( 123, N'John Doe', '20161001', 5000 ),
            ( 123, N'John Doe', '20161101', 5000 ),
            ( 123, N'John Doe', '20161201', 5000 ),
            ( 456, N'Jane Doe', '20160101', 6000 ),
            ( 456, N'Jane Doe', '20160201', 6000 ),
            ( 456, N'Jane Doe', '20160301', 6000 ),
            ( 456, N'Jane Doe', '20160401', 6000 ),
            ( 456, N'Jane Doe', '20160501', 6000 ),
            ( 456, N'Jane Doe', '20160601', 6000 ),
            ( 456, N'Jane Doe', '20160701', 6000 ),
            ( 456, N'Jane Doe', '20160801', 6000 ),
            ( 456, N'Jane Doe', '20160901', 6000 ),
            ( 456, N'Jane Doe', '20161001', 6000 ),
            ( 456, N'Jane Doe', '20161101', 6000 ),
            ( 456, N'Jane Doe', '20161201', 6000 );
       
    DECLARE @Increase TABLE
        (
          IncreaseID INT IDENTITY(1, 1)
                         NOT NULL ,
          IncreaseDate DATE NOT NULL ,
          IncreasePercent MONEY NOT NULL ,
          PRIMARY KEY ( IncreaseID ASC )
        );
          
    INSERT  @Increase
            ( IncreaseDate, IncreasePercent )
    VALUES  ( '20160401', 0.05 ),
            ( '20160901', 0.1 ),
            ( '20161101', 0.07 );
            
    
    WITH    BaseSalary
              AS ( SELECT  DISTINCT
                            PR.EmployeeNo ,
                            PR.EmployeeName ,
                            PR.Salary
                   FROM     @PayRoll PR
                 ),
            CumulativeIncrease
              AS ( SELECT   I.IncreaseDate ,
                            I.IncreasePercent ,
                            CI.CumulativeIncrease
                   FROM     @Increase I
                            CROSS APPLY ( SELECT    EXP(SUM(LOG(1 + I2.IncreasePercent)))
                                          FROM      @Increase I2
                                          WHERE     I2.IncreaseDate <= I.IncreaseDate
                                        ) CI ( CumulativeIncrease )
                 )
        SELECT  BS.EmployeeNo ,
                BS.EmployeeName ,
                BS.Salary ,
                CI.IncreaseDate ,
                BS.Salary * CI.CumulativeIncrease
        FROM    BaseSalary BS
                CROSS JOIN CumulativeIncrease CI
        ORDER BY 1 ,
                2 ,
                4;


    • Edited by Stefan Hoffmann Wednesday, December 30, 2015 2:22 PM
    • Proposed as answer by Naomi N Wednesday, December 30, 2015 7:37 PM
    Wednesday, December 30, 2015 2:04 PM
  • Can you change your approach or are you stuck with this table structure?

    Payrates are generally not handled this way.  Payrates normally have a begin and end date, in a separate table.  This makes what you describe much easier.

    • Proposed as answer by Naomi N Wednesday, December 30, 2015 7:38 PM
    Wednesday, December 30, 2015 2:44 PM
  • Thanks for the good DDL and example data. Really makes our job easier!

    Try this on for size:

    DECLARE @PayRoll TABLE ([PayRollID]    [INT] IDENTITY(1, 1) NOT NULL, [EmployeeNo]   [INT] NOT NULL, [EmployeeName] [VARCHAR](8) NOT NULL, [Month]        [DATE] NOT NULL, [Salary]       [MONEY] NOT NULL)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-01-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-02-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-03-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-04-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-05-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-06-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-07-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-08-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-09-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-10-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-11-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-12-01' AS Date), 5000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-01-01' AS Date), 6000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-02-01' AS Date), 6000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-03-01' AS Date), 6000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-04-01' AS Date), 6000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-05-01' AS Date), 6000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-06-01' AS Date), 6000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-07-01' AS Date), 6000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-08-01' AS Date), 6000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-09-01' AS Date), 6000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-10-01' AS Date), 6000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-11-01' AS Date), 6000)
    INSERT INTO @PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-12-01' AS Date), 6000)
    
    DECLARE @Increase TABLE ([IncreaseID]      [INT] IDENTITY(1, 1) NOT NULL, [IncreaseDate]    [DATE] NOT NULL, [IncreasePercent] [MONEY] NOT NULL)
    INSERT INTO @Increase ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-04-01' AS Date), 5.0000)
    INSERT INTO @Increase ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-09-01' AS Date), 10.0000)
    INSERT INTO @Increase ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-11-01' AS Date), 7.0000)
    
    ;WITH base AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY i.IncreaseDate) AS seq
      FROM @Increase i
    ), rCTE AS (
    SELECT a.increaseDate, a.IncreasePercent, a.seq, a.IncreaseDate AS minDate, 0 AS level
      FROM base a
    UNION ALL
    SELECT a.increaseDate, ((r.IncreasePercent/100)*a.IncreasePercent)+a.IncreasePercent, a.seq, r.IncreaseDate AS minDate, r.level+1
      FROM rCTE r
        INNER JOIN base a
    	  ON r.seq + 1 = a.seq
    ), empDateRange AS (
    SELECT EmployeeNo, MIN(Month) AS minMonth, MAX(Month) AS maxMonth
      FROM @PayRoll
     GROUP BY EmployeeNo
    )
    
    SELECT pr.*, COALESCE(pr.Salary*(r.IncreasePercent/100.0)+pr.Salary,pr.Salary) AS finalSalary, COALESCE(r.IncreasePercent,0.0) AS increasePercentFromBase
      FROM @PayRoll pr
        INNER JOIN empDateRange edr
    	  ON pr.EmployeeNo = edr.EmployeeNo
    	LEFT OUTER JOIN rCTE r
    	  ON pr.Month >= r.IncreaseDate
    	  AND edr.minMonth <= r.minDate
    	  AND r.level = (SELECT MAX(level) FROM rCTE WHERE pr.month >= IncreaseDate AND edr.minMonth <= minDate)
    


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, December 30, 2015 2:47 PM
  • huh, pretty complex ;)

    Wednesday, December 30, 2015 3:18 PM
  • Hi rblomstrand,

    Check if the below query leads to the expected output.

    ;WITH Cte AS
    (
    SELECT IncreaseDate,IncreasePercent,ROW_NUMBER() OVER(ORDER BY IncreaseDate) RN FROM Increase
    )
    ,Cte2 AS
    (
    SELECT c.IncreaseDate startDate,
    	   COALESCE(C2.IncreaseDate ,'99990101') endDate,
    	   SUM(c.IncreasePercent) OVER(ORDER BY C.IncreaseDate) accumulativePercentage
    	   FROM Cte C LEFT JOIN Cte C2 ON C.RN=C2.RN-1
    )
    SELECT P.PayRollID,
    	 P.EmployeeNo,
    	 P.EmployeeName,
    	 P.Month,
    	 P.Salary*(COALESCE(accumulativePercentage,0)/100+1) Salary  
    	 FROM PayRoll P LEFT JOIN Cte2 C 
    			  ON P.Month >= C.startDate AND P.Month <endDate
    
     

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support

    Thursday, December 31, 2015 7:05 AM
  • Hi ,

    I am getting different result. I used SQCLR User-Defined Aggregate Function.
    It give great performance :-)
    Unfortunately, this is not Windows function so I use sub-query

    * I will post the whole SQLCLR code in several minute in a blog. 

    But I am getting different result from Eric (I think that My result correct if I did not done any simple math mistake).

    This is my solution using my function:

    SELECT 
    	o.PayRollID, o.EmployeeNo, o.EmployeeName, o.[Month], o.Salary
    	, finalSalary =  o.Salary * ((  (SELECT dbo.ArielyPercentageAgg([IncreasePercent]) FROM [Increase] i where i.IncreaseDate <= o.[Month]) + 100 ) / 100 )
    	,increasePercentFromBase = (SELECT dbo.ArielyPercentageAgg([IncreasePercent]) FROM [Increase] i where i.IncreaseDate <= o.[Month])  
    FROM PayRoll o
    GO

    Here is the screenshot of the result:


    * The function rounds the result to 2 chars after the zero. This can be change in the CREATE AGGREGATE easily if needed, by replacing the decimal in the input and in the return into decimal(32,10) for example.

    Happy New Year to everyone :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]




    • Edited by pituachMVP Friday, January 1, 2016 1:26 AM
    • Marked as answer by Eric__Zhang Friday, January 8, 2016 6:13 AM
    Thursday, December 31, 2015 6:25 PM
  • Here is the full code for the SQCLR User-Defined Aggregate Function:

    http://ariely.info/Blog/tabid/83/EntryId/171/SQLCLR-Percentage-User-Defined-Aggregate-Functions.aspx

    I hope this is useful :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, December 31, 2015 6:34 PM
  • For the record, Ronen, my solution provides the same results as yours, except yours seem to be rounded up.

    For 2016-11-01 You say 7.74% / 5387.00, I say 7.735 / 5386.75, Eric Says 6100 (which seems to be 18.03%)

    I mention this as it might be important to the OP.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    Thursday, December 31, 2015 6:39 PM
  • If the OP have question I will be happy to answer :-)

    With that said, I will try to avoid discussion with you in the future or respond, other than this general message (since I did not found option to send it on private). As a result of your behavior several days ago in the forum, You lost my respect to you!
    * I write this as individual and not as moderator.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Wednesday, January 13, 2016 11:42 PM
    Thursday, December 31, 2015 7:02 PM
  • Hi Ronen,

    with respect but your "evidence" fail extreme. What you show is only an execution plan with estimates (as an execution plan will ALWAYS be in SSMS!). The real costs can only be visible with Trace or XE as the following demo will show:

    CREATE TABLE dbo.master
    (
    	id	int			not null	identity(1, 1),
    	c1	char(20)	not null	DEFAULT ('filler')
    );
    GO
    
    -- Clustered index for better performance
    CREATE UNIQUE CLUSTERED INDEX cix_master_id ON dbo.master (id);
    GO
    
    CREATE TABLE dbo.detail
    (
    	master_id		int			NOT NULL,
    	detail_date		datetime	NOT NULL,
    	c1				char(1000)	NOT NULL	DEFAULT ('filler'),
    	detail_value	money		NOT NULL
    );
    
    -- Clustered index for better performance
    CREATE UNIQUE CLUSTERED INDEX cix_detail ON dbo.detail
    (
    	master_id,
    	detail_date
    );
    GO
    
    CREATE FUNCTION dbo.sum(@master_id int, @startdate datetime, @enddate datetime) RETURNS money
    AS
    BEGIN
    	DECLARE	@return_value	money;
    
    	SELECT	@return_value	= SUM(detail_value)
    	FROM	dbo.detail
    	WHERE	master_id = @master_id AND
    			detail_date >= @startdate AND
    			detail_date < @enddate;
    
    	RETURN	@return_value;
    END
    GO
    
    SET NOCOUNT ON;
    GO
    
    -- demo data
    INSERT INTO dbo.master (c1) VALUES ('demo record 01');
    GO
    
    INSERT INTO dbo.detail (master_id, detail_date, detail_value)
    VALUES (1, DATEADD(day, CAST(RAND() * 100 AS int), getdate()), RAND() * 10000);
    GO 1000

    The above code creates two tables and a scalar function. The master table has 1 record and the details have 1,000 records. The next demo shows two queries which will return exactly the same result (technical and functional!)

    -- Base query!
    SELECT	m.Id,
    		m.c1,
    		SUM(d.detail_value)	AS	Amount
    FROM	dbo.master AS m INNER JOIN dbo.detail AS d
    		ON (m.id = d.master_id)
    WHERE	m.id = 1
    		AND	d.detail_date >= '20160201'
    		AND d.detail_date < '20160301'
    GROUP BY
    		m.id,
    		m.c1;
    GO
    
    -- query with function
    SELECT	m.Id,
    		m.c1,
    		dbo.SUM(m.id, '20160201', '20160301')	AS	Amount
    FROM	dbo.master AS m
    WHERE	m.id = 1
    GROUP BY
    		m.id,
    		m.c1;
    GO

    Following your statement the second one is the REALY MUCH BETTER performance boost ever, isn't it?

    WOW - the solution with the function is much better because it only takes 6% of the time. But this is not true because the access to the dbo.detail is not in the plan. The trace looks completely different and shows the REAL solution.

    So my conclusion about functions in T-SQL:

    • Don't trust execution plans they are estimates
    • check the REAL IO and Time
    • Microsoft SQL Server is a relational system which works best with sets but not with single records / cursors


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Thursday, February 4, 2016 9:13 AM
  • That is true!

    +1

    But I did not say that you should only use EP :-)
    It is out of the thread issue to write everything.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, February 4, 2016 12:14 PM
  • But I did not say that you should only use EP :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Good Morning Ronen,

    but you said: "It give great performance :-)" in your answer to the TE. And I - to be honest - doubt about this statement. A function within the output is from my experience the worst performance killer in a RDBMS.

    I'm with you when you say: "... to write everything" but if someone is giving advices in a forum all benefits and drawbacks should be mentioned. This will give the TE a decision tree for his solution.

    All the best to you 'til next time...


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Friday, February 5, 2016 5:20 AM