Sql-server – Best way of order processing

order-byperformancesql serverstored-procedurestransaction

I would like to write a procedure for order processing
Single Order can have multiple items, how can I process the order?

Consider below JSON as an example:

{
    "Order":
    {
        "OrderId":"",
        "OrderDate":"",
        "CustomerId":"",
        "Price":300,
        "Items":
        [
            {
                "ItemId":103,
                "Qty":2,
                "Price":100
            },
            {
                "ItemId":123,
                "Qty":2,
                "Price":100
            },
            {
                "ItemId":122,
                "Qty":2,
                "Price":100
            }   
        ]   
    }
}

Corresponding objects:

public class Item
{
    public int ItemId { get; set; }
    public int Qty { get; set; }
    public int Price { get; set; }
}

public class Order
{
    public string OrderId { get; set; }
    public string OrderDate { get; set; }
    public string CustomerId { get; set; }
    public int Price { get; set; }
    public List<Item> Items { get; set; }
}

I found two ways to process the above order

  1. Prepare complete order items as a string like ItemId|Price|Qty,ItemId|Price|Qty,ItemId|Price|Qty (split functionality inside procedure)

    @orderDate datetime = null,
@itemStr nvarchar(2000) = null, --(split functionality inside procedure)
@orderPrice decimal(18,2) = 0.00,
@customerId int = null

just find my approach based on my parameters declaration.

  1. First order creation done separately and order under items will be inserted separately.
    (programmatic insertion instead of native procedure call, no need to implement split functionality inside procedure).

Looking for best way of order processing (transaction management and performance are major constraints)

Best Answer

This is probably best to insert all items at once or at least avoid have to split string on the SQL Server side.

You could convert json to xml similar to this:

<Order OrderId="" OrderDate="" CustomerId="" Price="300">
    <Items>
        <Item ItemId="103" Qty="2" Price="100" />
        <Item ItemId="123" Qty="2" Price="100" />
        <Item ItemId="122" Qty="2" Price="100" />
    </Items>
</Order>

You can then execute this stored procedure with this xml parameter:

CREATE PROC InsertItems(
    @xml xml
) AS
BEGIN
    SET NOCOUNT ON  

    INSERT INTO data(ItemId, Qty, Price)
    SELECT x.value('@ItemId', 'int') as ItemId
        , x.value('@Qty', 'int') as Qty
        , x.value('@Price', 'int') as Price
    FROM @xml.nodes('/Order/Items/Item') t(x)
END

You must add the nodes and value path to your xml.

It should work fine for a few rows but it may not be very efficient with hundreds or thousands of Items.

You can also create a Table Type and pass is to the stored procedure using a Table-Valued Parameters:

CREATE TYPE dbo.ItemsTableType AS TABLE
    ( ItemId int, Qty int, Price int)

CREATE PROCEDURE insertItems
    (@items dbo.ItemsTableType READONLY)
AS
BEGIN
    SET NOCOUNT ON  

    INSERT INTO data(ItemId, Qty, Price)
    SELECT ItemId, Qty, Price
    FROM @items 
END

You then call it from your code using one of the .Net options and examples on this page: Table-Valued Parameters.

If you have to insert too many rows at once, you can split it in batch of 100, 1000 or whatever works best on your system.

Related Question