时间戳

首页 > 解决方案 > 如何用不同的机制替换 SQL Server 游标

json - 如何用不同的机制替换 SQL Server 游标

问题描述

我正在使用 SQL Server 2008。我必须从两个具有一对多关系的表创建 json。表格是Customer和Orders。

每个客户可能有一个或多个订单。json 是通过首先从客户表中获取数据，然后附加他们所做的所有购买来构建的。

以下是我的查询。我还附上了查询的 json 输出。它可以工作并创建有效的 json。问题是它太慢了，因为我使用游标循环遍历Customer表格。我已经设法避免Orders使用用于 xml 路径的光标从表中获取数据。由于我必须处理数百万行，我必须用其他机制替换游标。

DECLARE @PaymentType VARCHAR(50),
        @Email VARCHAR(100), 
        @OrderId INT

DECLARE CustomerCursor CURSOR FAST_FORWARD FOR
    SELECT TOP 10 
        PaymentType, Email, OrderId 
    FROM 
        CUSTOMER

OPEN CustomerCursor

FETCH NEXT FROM CustomerCursor INTO @PaymentType, @Email, @OrderId

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE @customer VARCHAR(MAX)
    DECLARE @order VARCHAR(MAX)
    DECLARE @customer_with_order VARCHAR(MAX)

    -- construct order json
    SET @order =  '[' + STUFF((SELECT ',{"orderProductID":' + CAST(orderProductID AS VARCHAR) + 
                                      ',"productType":"' + ProductType + '"' + 
                                      ',"productName":"' + ProductName + '"' +
                                      ',"categoryName":"' + CategoryName + '"' + '}'
                               FROM ORDERS 
                               WHERE orderid = @OrderId
                               FOR XML PAT(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') + ']'
    -- construct customer json
    SET @customer = '{"email":"' + CASE WHEN @Email IS NULL THEN '' ELSE 
   @Email END + '"'
                + ',"eventName": "ChristmasSale", "dataFields": {' 
                + '"orderId":' + CAST(CASE WHEN @OrderId IS NULL THEN 0 ELSE 
    @OrderId  END AS VARCHAR)                   
                + ',"paymentType":"' + CASE WHEN @PaymentType IS NULL THEN 
    '' ELSE @PaymentType END + '"'                  
                + ',"products": '

    -- combine these two
    SET @customer_with_order = @customer + @order + '}}'

    -- insert into CUSTOMER_ORDER_DATA
    INSERT INTO CUSTOMER_ORDER_DATA(email, order_id, orders) 
    VALUES (@Email, @OrderId, @customer_with_order)

    FETCH NEXT FROM CustomerCursor INTO @PaymentType, @Email, @OrderId
END

CLOSE CustomerCursor
DEALLOCATE CustomerCursor

在此处输入图像描述

标签： jsonsql-server

解决方案

我无法对此进行测试，但我怀疑您可以将上面的内容重写为基于集合的方法，如下所示（因为我无法测试这个，我无法确定这会起作用，如果它不，您可能需要对其进行一些故障排除）：

INSERT INTO CUSTOMER_ORDER_DATA(email, order_id, orders)
SELECT C.Email,
       C.orderid,
       '{"email":"' + CASE WHEN @Email IS NULL THEN '' ELSE 
   @Email END + '"'
                + ',"eventName": "ChristmasSale", "dataFields": {' 
                + '"orderId":' + CAST(CASE WHEN @OrderId IS NULL THEN 0 ELSE 
    @OrderId  END AS varchar)                   
                + ',"paymentType":"' + CASE WHEN @PaymentType IS NULL THEN 
    '' ELSE @PaymentType END + '"'                  
                + ',"products": ' +
    ('[' + STUFF((
    SELECT 
        ',{"orderProductID":' + CAST(orderProductID AS varchar)
        + ',"productType":"' + ProductType + '"'
        + ',"productName":"' + ProductName + '"'
        + ',"categoryName":"' + CategoryName + '"'          
        +'}'
    FROM ORDERS AS O
    WHERE O.orderid = C.orderid
    FOR XML PATH(''),TYPE).value('.', 'varchar(max)'), 1, 1, '') + ']')
FROM CUSTOMER AS C

考虑到 OP 有 500 万行，那么这对于一批来说可能有点多。将其分成 10,000 个批次可能对整体性能更好。不幸的是，OP 仍在使用 2008，因此他们无权访问该OFFSET子句。

推荐阅读