4 Comments

1. 

   Art on 2025-02-09 at 04:04

   Here is my KISS solution (it works in dev!):

   CREATE NONCLUSTERED INDEX nix_orders_o_custkey_o_totalprice
   ON dbo.orders(o_custkey, o_orderdate) INCLUDE (o_totalprice)
   GO

   ALTER FUNCTION [dbo].[cf_customer_sales_volume]
   (
   @o_custkey BIGINT,
   @sales_year INT
   )
   RETURNS MONEY
   AS
   BEGIN
   DECLARE @result_sales MONEY;
   DECLARE @year_lower_limit date = CAST(CAST(@sales_year AS CHAR(4)) AS DATE)
   DECLARE @year_upper_limit date = CAST(CAST(@sales_year + 1 AS CHAR(4)) AS DATE)

   SELECT @result_sales = SUM (o_totalprice)
   FROM dbo.orders
   WHERE o_custkey = @o_custkey
   AND (o_orderdate >= @year_lower_limit AND o_orderdate < @year_upper_limit)

   RETURN @result_sales;
   END
   GO

   Reply
   • 

     Art on 2025-02-14 at 17:28

     Since posting, I have learned about DATEFROMPARTS function which should be used instead of casting.

     I have learned this from relevant video by Erik Darling
     https://erikdarling.com/better-filtering-with-year-and-month-functions-in-sql-server/

     Reply
2. 

   Henrik Staun Poulsen on 2025-02-09 at 18:00

   I’m not sure if I understand your condition:
   The parameters used to calculate [sales_volume] must not be changed!
   But here is my suggestion:
   A trigger on dbo.Orders.
   Brute force, so any update on a customer, requires a read of all the orders for this customer.
   So it can be made faster, if I dared try to figure out all the combinations of updates, deletes, inserts.

   alter TABLE dbo.customers DROP COLUMN sales_volume
   –alter TABLE dbo.customers add [sales_volume] AS ([dbo].[cf_customer_sales_volume]([c_custkey],(2023)))

   alter TABLE dbo.customers ADD sales_volume MONEY NOT NULL CONSTRAINT cust_SalesVolume DEFAULT (0)
   go
   CREATE OR ALTER TRIGGER dbo.SalesVolume ON dbo.Orders
   AFTER INSERT, UPDATE, DELETE AS
   BEGIN
   SET XACT_ABORT, NOCOUNT ON
   UPDATE c
   SET sales_volume = ISNULL(o.o_totalprice,0)
   FROM dbo.customers c
   INNER JOIN (
   SELECT o.o_custkey, SUM (o_totalprice) AS o_totalprice
   FROM dbo.orders o
   WHERE YEAR(o.o_orderdate) = 2023
   GROUP BY o.o_custkey
   ) o ON o.o_custkey = c.c_custkey
   WHERE EXISTS(SELECT * FROM inserted i where i.o_custkey = c.c_custkey)
   OR
   EXISTS(SELECT * FROM deleted d where d.o_custkey = c.c_custkey)
   END

   UPDATE orders SET o_orderdate=o_orderdate WHERE 1=1

   Reply
3. 

   Calin on 2025-02-10 at 14:24

   Assuming you are NOT allowed to change the queries, the answer is an indexed view (I know, I know, I am just opening a new can of worms here), but let’s test this scenario in AdventureWorks:

   CREATE FUNCTION dbo.CalculateTotalDueForCustomer(@CustomerID INT)
   RETURNS DECIMAL(18, 2)
   AS
   BEGIN
   DECLARE @TotalDue DECIMAL(18, 2);

   SELECT @TotalDue = SUM(TotalDue) — just to have a scalar function
   FROM Sales.SalesOrderHeader
   WHERE CustomerID = @CustomerID;

   RETURN ISNULL(@TotalDue, 0);
   END;

   ALTER TABLE Sales.SalesOrderHeader
   ADD TotalDueForCustomer AS dbo.CalculateTotalDueForCustomer(CustomerID);

   Let us consider the following query:

   SELECT SalesOrderID, CustomerID, TotalDue, TotalDueForCustomer
   FROM Sales.SalesOrderHeader
   WHERE CustomerID BETWEEN 11000 AND 30000 — intervals are giving us headaches
   ORDER BY CustomerID;

   I only put ORDER BY to have a clearer picture.

   On my machine, the query takes 326312 reads for 860 ms for CPU (Profiler, RPC completed). DO NOT use SET STATISTICS IO, TIME ON, does not reveal useful results here.

   Now let’s create the indexed view on the column of interest.

   CREATE VIEW [dbo].[vw_CustomerSales]
   WITH SCHEMABINDING
   AS
   SELECT
   so.CustomerID,
   COUNT_BIG(*) AS TotalOrders, — COUNT_BIG for it be be indexed
   SUM(so.TotalDue) AS TotalSales
   FROM
   Sales.SalesOrderHeader AS so
   GROUP BY
   so.CustomerID;
   GO

   CREATE UNIQUE CLUSTERED INDEX IX_CustomerSales ON dbo.vw_CustomerSales (CustomerID);

   Now re-run the exact same query as above.

   On my machine, the IO is now 62412 with a CPU of 485. So 5 times less IO with 40%+ drop in CPU consumption as well. Again, this is WITHOUT touching any other on the server.

   Reply