Geek asks and answers

SQL update. Commit each row in order.

Good morning. I will do my best to explain my question without publishing SQL (this is 650 lines). Let me know if additional information is needed.

We have an internal execution system that allocates resources in real time. For the distribution to work correctly, we need to know how much inventory is available each time the user asks what they should work on (loading / reloading their task list). The data looks something like this:

ID    ItemID    QtyOrdered    QtyAvailableAfterAllocation    ParentID
1     1234      5             500                            NULL
2     1234      15            485                            1
3     1234      10            475                            2

The while loop is currently used to set the QtyAvailableAfterAllocation column . The above example demonstrates the need for a loop. Line 2 QtyAvailableAfterAllocation depends on the value of line 1 QtyAvailableAfterAllocation. Line 3 depends on line 2, etc.

This is a (very) simplified version of the logic. It becomes infinitely more complex when you take into account sets (groups of inventory items belonging to the same parent element). There are times when the inventory does not need to be allocated to this element, because it exists inside a set that has sufficient stock to complete the order. That is why we cannot fulfill the total amount. In addition, sets can be nested inside sets at level N. This is the problem. When working with a large number of orders with nested sets, query performance is very poor. I believe the cycle is to blame (testing proved this). So here is the question:

, ( ), () (QtyAvailAfterOrder_AllocationScope) ?

SQL. while. , , .

http://pastebin.com/VM9iasq9

+3
4

/ , #. PLINQ, 15 4. , !

, ( , ).

0

- ?

DECLARE @CurrentCount int
SELECT @CurrentCount = QtyAvailableAfterAllocation 
FROM blah 
WHERE <select the parent of the first row>

UPDATE blah
SET QtyAvailableAfterAllocation = @CurrentCount - QtyOrdered,
    @CurrentCount = @CurrentCount - QtyOrdered
WHERE <it is valid to deduct the count>

. - WHERE.

, , - ( , N- ) , . . , , .

, .

, , , , - .

+1

, - , SQL ; "" "SQL", , : SQL , .

, , :

        pseudo:
        WHILE REQUESTS_REMAIN_IN_QUEUE

             begin trans
                execute requisition SQL statements
             commit

        LOOP

() :

           update inventory
           set QOH = QOH- {requested amount}
           where  partno = ? and QOH >= {requested amount}

           insert orderdetail
           (customer, orderheaderid, partno, requestedamount)
           values
           (custid, orderheaderid, partno, requested_amount)

, -, , , , . , , , .

0

, .

, :

  • .

  • , .

  • QtyAvailableAfterAllocation .

:

/* sample data definition & initialisation */
DECLARE @LastQty TABLE (Item int, Qty int);
INSERT INTO @LastQty (Item, Qty) 
  SELECT 0123, 404 UNION ALL
  SELECT 1234, 505 UNION ALL
  SELECT 2345, 606 UNION ALL
  SELECT 3456, 707 UNION ALL
  SELECT 4567, 808 UNION ALL
  SELECT 5678, 909;
DECLARE @Orders TABLE (ID int, Item int, OrderedQty int);
INSERT INTO @Orders (ID, Item, OrderedQty)
  SELECT 1, 1234,  5 UNION ALL
  SELECT 2, 1234, 15 UNION ALL
  SELECT 3, 2345,  3 UNION ALL
  SELECT 4, 1234, 10 UNION ALL
  SELECT 5, 2345, 37 UNION ALL
  SELECT 6, 2345, 45 UNION ALL
  SELECT 7, 3456, 50 UNION ALL
  SELECT 8, 4567, 25 UNION ALL
  SELECT 9, 2345, 30;

/* the actuall query begins here */
WITH RankedOrders AS (
  SELECT
    *,
    rn = ROW_NUMBER() OVER (PARTITION BY Item ORDER BY ID)
  FROM @Orders
),
RunningOrderTotals AS (
  SELECT
    ID,
    Item,
    OrderedQty,
    RunningTotalQty = OrderedQty,
    rn
  FROM RankedOrders
  WHERE rn = 1
  UNION ALL
  SELECT
    o.ID,
    o.Item,
    o.OrderedQty,
    RunningTotalQty = r.RunningTotalQty + o.OrderedQty,
    o.rn
  FROM RankedOrders o
    INNER JOIN RunningOrderTotals r ON o.Item = r.Item AND o.rn = r.rn + 1
)
SELECT
  t.ID,
  t.Item,
  t.OrderedQty,
  QtyAvailableAfterAllocation = oh.Qty - t.RunningTotalQty
FROM RunningOrderTotals t
  INNER JOIN @LastQty oh ON t.Item = oh.Item
ORDER BY t.ID;

Note. For my example, I initialized the table of quantity of available goods ( @LastQty) manually. However, you are most likely going to extract it from your data.

0
source

More articles:


All Articles