如何将游标优化为基于集合的运算符查询？

Question

我的存储过程中有游标。我已经更新到while循环，以便更快地执行。但是我的while循环也需要同样长的时间。请帮我调试我的脚本。请找到我的以下代码。

我需要在更新游标的帮助下，在MS SQL中设置基于操作员查询。

DECLARE @orderArray INT
    ,@LeftTSMKEY NVARCHAR(250)
    ,@ListElid NVARCHAR(250)
    ,@ListType NVARCHAR(250)
    ,@ListType_Prev NVARCHAR(250)

SET @ListType_Prev = ''
SET @inc = 0

DECLARE cursql CURSOR
FOR
SELECT ListElid
    ,ListType
    ,orderArray
    ,LeftTSMKEY
FROM QAT_ListElid
ORDER BY ListType
    ,orderArray

OPEN cursql

FETCH NEXT
FROM cursql
INTO @ListElid
    ,@ListType
    ,@orderArray
    ,@LeftTSMKEY

WHILE (@@Fetch_status = 0)
BEGIN
    IF @ListType <> @ListType_Prev
    BEGIN
        SET @inc = 0
        SET @ListType_Prev = @ListType
    END
    ELSE IF @ListType = @ListType_Prev
    BEGIN
        SET @inc = @inc + 1
    END

    IF @inc <> @orderArray
    BEGIN
        INSERT [QAT_ListElid2] (
            [ListElid]
            ,[ListType]
            ,[orderArray]
            ,[LeftTSMKEY]
            )
        VALUES (
            @ListElid
            ,@ListType
            ,@inc
            ,@LeftTSMKEY + CAST(@inc AS NVARCHAR(10)) + ']'
            )
    END

    FETCH NEXT
    FROM cursql
    INTO @ListElid
        ,@ListType
        ,@orderArray
        ,@LeftTSMKEY
END

CLOSE cursql

DEALLOCATE cursql

请找到下面的示例数据

​

​

ListElid    ListType    orderArray  LeftTSMKEY
1000:odl5:7pt_ToxAcuDo[0]   1000:odl5:7pt_ToxAcuDo  0   ToxAcuDo[
106i:odl5:7pt_ToxAcuDo[0]   106i:odl5:7pt_ToxAcuDo  0   ToxAcuDo[
107:107:7pt_NIL[0]  107:107:7pt_NIL 0   NIL[
107:1827:7pt_NIL[0] 107:1827:7pt_NIL    0   NIL[
107:1827:7pt_NIL[1] 107:1827:7pt_NIL    1   NIL[
107:1827:7pt_NIL[3] 107:1827:7pt_NIL    3   NIL[
107:1hqn:7pt_NIL[0] 107:1hqn:7pt_NIL    0   NIL[
107:1hqn:7pt_NIL[1] 107:1hqn:7pt_NIL    1   NIL[
107:1rj7:7pt_NIL[0] 107:1rj7:7pt_NIL    0   NIL[
107:1rj7:7pt_NIL[1] 107:1rj7:7pt_NIL    1   NIL[
107:1rsg:7pt_NIL[0] 107:1rsg:7pt_NIL    0   NIL[
107:1s2r:7pt_NIL[0] 107:1s2r:7pt_NIL    0   NIL[
107:1s2r:7pt_NIL[1] 107:1s2r:7pt_NIL    1   NIL[
107:1s2r:7pt_NIL[2] 107:1s2r:7pt_NIL    2   NIL[
107:1s2r:7pt_NIL[4] 107:1s2r:7pt_NIL    4   NIL[
107:1vf:7pt_ NIL[0] 107:1vf:7pt_NIL    0    NIL[

Answer 1

它们基于DECLARE CURSOR语法，主要用于Transact-SQL脚本、存储过程和触发器。Transact-SQL游标在服务器上实现，并由从客户端发送到服务器的Transact-SQL语句管理。它们也可以包含在批处理、存储过程或触发器中。

与INT、VARCHAR和其他数据类型一样，游标是在T-SQL批处理中声明的。SQL Server附带了管理它的相关说明：

1. OPEN可告知SQL Server运行查询并使用结果集填充游标；
2. CLOSE可告知SQL Server释放游标使用的资源；
3. FETCH可告知SQL Server从游标中检索特定行。当我们使用这个函数时，我们可以在@@FETCH_STATUS变量中得到FETCH指令的结果。如果设置为0，则表示指令成功。

Answer 2

如果我没弄错的话，下面这句话是一个可能的解决方案。

表：

CREATE TABLE QAT_ListElid (
    ListElid nvarchar(250),  
    ListType nvarchar(250), 
    orderArray int, 
    LeftTSMKEY nvarchar(250)
)
INSERT INTO QAT_ListElid
    (ListElid, ListType, orderArray, LeftTSMKEY)
VALUES
    ('1000:odl5:7pt_ToxAcuDo[0]',   '1000:odl5:7pt_ToxAcuDo',   0, 'ToxAcuDo['),
    ('106i:odl5:7pt_ToxAcuDo[0]',   '106i:odl5:7pt_ToxAcuDo',   0, 'ToxAcuDo['),
    ('107:107:7pt_NIL[0]',          '107:107:7pt_NIL',          0, 'NIL['),
    ('107:1827:7pt_NIL[0]',         '107:1827:7pt_NIL',         0, 'NIL['),         -- 107:1827:7pt_NIL
    ('107:1827:7pt_NIL[1]',         '107:1827:7pt_NIL',         1, 'NIL['),         -- 107:1827:7pt_NIL
    ('107:1827:7pt_NIL[3]',         '107:1827:7pt_NIL',         3, 'NIL['),         -- 107:1827:7pt_NIL
    ('107:1hqn:7pt_NIL[0]',         '107:1hqn:7pt_NIL',         0, 'NIL['),
    ('107:1hqn:7pt_NIL[1]',         '107:1hqn:7pt_NIL',         1, 'NIL['),
    ('107:1rj7:7pt_NIL[0]',         '107:1rj7:7pt_NIL',         0, 'NIL['),
    ('107:1rj7:7pt_NIL[1]',         '107:1rj7:7pt_NIL',         1, 'NIL['),
    ('107:1rsg:7pt_NIL[0]',         '107:1rsg:7pt_NIL',         0, 'NIL['),
    ('107:1s2r:7pt_NIL[0]',         '107:1s2r:7pt_NIL',         0, 'NIL['),         -- 107:1s2r:7pt_NIL
    ('107:1s2r:7pt_NIL[1]',         '107:1s2r:7pt_NIL',         1, 'NIL['),         -- 107:1s2r:7pt_NIL
    ('107:1s2r:7pt_NIL[2]',         '107:1s2r:7pt_NIL',         2, 'NIL['),         -- 107:1s2r:7pt_NIL
    ('107:1s2r:7pt_NIL[4]',         '107:1s2r:7pt_NIL',         4, 'NIL['),         -- 107:1s2r:7pt_NIL
    ('107:1vf:7pt_ NIL[0]',         '107:1vf:7pt_NIL',          0, 'NIL[')
CREATE TABLE QAT_ListElid2 (
    ListElid nvarchar(250),  
    ListType nvarchar(250), 
    orderArray int, 
    LeftTSMKEY nvarchar(250)
)

声明：

;WITH ChangesCTE AS (
    SELECT 
        ListElid, ListType, orderArray, LeftTSMKEY,
        CASE 
            WHEN ListType = LAG(ListType) OVER (PARTITION BY ListType ORDER BY orderArray) THEN 1 
            ELSE 0
        END AS [Change]
    FROM QAT_ListElid
), FinalCTE AS (
    SELECT 
        ListElid, ListType, orderArray, LeftTSMKEY,
        SUM([Change]) OVER (PARTITION BY ListType ORDER BY ListType, orderArray) AS [inc]
    FROM ChangesCTE
)
INSERT INTO QAT_ListElid2
    ([ListElid], [ListType], [orderArray], [LeftTSMKEY])
SELECT 
    ListElid, 
    ListType, 
    [inc] AS orderArray, 
    LeftTSMKEY + CAST([inc] as nvarchar(10)) + ']' AS LeftTSMKEY
FROM FinalCTE
WHERE orderArray <> [inc]

或另一条语句作为第二个选项：

INSERT INTO QAT_ListElid2
    ([ListElid], [ListType], [orderArray], [LeftTSMKEY])
SELECT 
    ListElid, 
    ListType, 
    [inc] AS orderArray, 
    LeftTSMKEY + CAST([inc] as nvarchar(10)) + ']' AS LeftTSMKEY
FROM (
    SELECT 
        ListElid, ListType, orderArray, LeftTSMKEY,
        ROW_NUMBER() OVER (PARTITION BY ListType ORDER BY orderArray) - 1 AS [Inc]
    FROM QAT_ListElid
) t
WHERE orderArray <> [inc]

结果(两个插入的行)：

--------------------------------------------------------------
ListElid            ListType            orderArray  LeftTSMKEY
--------------------------------------------------------------
107:1827:7pt_NIL[3] 107:1827:7pt_NIL    2           NIL[2]
107:1s2r:7pt_NIL[4] 107:1s2r:7pt_NIL    3           NIL[3]