我能否将长时间运行的存储过程分散到多个CPU上？

Question

[另外在SuperUser上查看 - https://superuser.com/questions/116600/can-i-spead-out-a-long-running-stored-proc-accross-multiple-cpus]

我在SQL Server中有一个存储过程，用于获取和解密一块数据（在这种情况下是信用卡信息）。

大多数情况下，性能还可以接受，但有几个客户的处理速度非常慢，需要长达1分钟才能完成。（确切地说，从SQL Server返回需要59377ms，但根据负载情况可能会有几百毫秒的差异）

当我观察该进程时，我发现SQL仅使用单个处理器来执行整个过程，通常只使用处理器0。

是否有一种方法可以更改我的存储过程，以便SQL可以多线程处理该过程？是否可以欺骗并将调用分成两半（前50％，后50％），并分散负载，作为粗略的hack？（这里只是草率地想出的）

我的存储过程：

USE [Commerce]
GO
/****** Object:  StoredProcedure [dbo].[GetAllCreditCardsByCustomerId]    Script Date: 03/05/2010 11:50:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetAllCreditCardsByCustomerId]
@companyId UNIQUEIDENTIFIER, @DecryptionKey NVARCHAR (MAX)
AS
SET NoCount ON

DECLARE @cardId uniqueidentifier
DECLARE @tmpdecryptedCardData VarChar(MAX);
DECLARE @decryptedCardData VarChar(MAX);

    DECLARE @tmpTable as Table 
    (
        CardId uniqueidentifier,
        DecryptedCard NVarChar(Max)
    )

DECLARE creditCards CURSOR FAST_FORWARD READ_ONLY
  FOR  Select cardId from CreditCards where companyId = @companyId and Active=1 order by addedBy desc



--2 
OPEN creditCards
--3 
FETCH creditCards INTO @cardId   -- prime the cursor

WHILE @@Fetch_Status = 0 
  BEGIN

        --OPEN creditCards
        DECLARE creditCardData CURSOR FAST_FORWARD READ_ONLY
                        FOR select convert(nvarchar(max), DecryptByCert(Cert_Id('Oh-Nay-Nay'), EncryptedCard, @DecryptionKey)) FROM CreditCardData where cardid = @cardId order by valueOrder

                OPEN creditCardData

                FETCH creditCardData INTO @tmpdecryptedCardData   -- prime the cursor

                WHILE @@Fetch_Status = 0 
                    BEGIN               

                        print 'CreditCardData'
                        print @tmpdecryptedCardData                     

                        set @decryptedCardData = ISNULL(@decryptedCardData, '') + @tmpdecryptedCardData
                        print '@decryptedCardData'
                        print @decryptedCardData;

                        FETCH NEXT FROM creditCardData INTO @tmpdecryptedCardData   -- fetch next
                    END 
                    CLOSE creditCardData
                    DEALLOCATE creditCardData       

                    insert into @tmpTable (CardId, DecryptedCard) values (  @cardId, @decryptedCardData )
                    set @decryptedCardData = ''


    FETCH NEXT FROM creditCards INTO @cardId   -- fetch next
  END

select CardId, DecryptedCard FROM @tmpTable


CLOSE creditCards
DEALLOCATE creditCards

- Russ

---

你在那里有一个光标内嵌在另一个光标中。@@FETCH_STATUS会变得相当困惑，你的过程可能会过早终止。请参考http://msdn.microsoft.com/en-us/library/ms187308.aspx。 - MartW

Answer 1

使用FOR XML在单个相关子查询中进行连接如何？

DECLARE @cards TABLE
    (
     cardid INT NOT NULL
    ,addedBy INT NOT NULL
    )
DECLARE @data TABLE
    (
     cardid INT NOT NULL
    ,valueOrder INT NOT NULL
    ,encrypted VARCHAR(MAX) NOT NULL
    )

INSERT  INTO @cards
VALUES  ( 0, 1 )
INSERT  INTO @cards
VALUES  ( 1, 0 )

INSERT  INTO @data
VALUES  ( 0, 0, '0encrypted0' )
INSERT  INTO @data
VALUES  ( 0, 1, '0encrypted1' )
INSERT  INTO @data
VALUES  ( 0, 2, '0encrypted2' )
INSERT  INTO @data
VALUES  ( 1, 0, '1encrypted0' )
INSERT  INTO @data
VALUES  ( 1, 1, '1encrypted1' )

-- INSERT INTO output_table ()
SELECT  cardid, decrypted
FROM    @cards AS cards
        OUTER APPLY ( SELECT    REPLACE(encrypted, 'encrypted', 'decrypted') + '' -- Put your UDF here
                      FROM      @data AS data
                      WHERE     data.cardid = cards.cardid
                      ORDER BY  data.valueOrder
                    FOR
                      XML PATH('')
                    ) AS data ( decrypted )
ORDER BY cards.addedBy DESC

- Cade Roux

---

我没有兴趣将其与 OP 中可怕的存储过程进行测试，但我绝对赞同摆脱那些游标（以及 Schlemiel-the-Painter 字符串拼接），这是任何一种方法都可以做到的。+1。 - Aaronaught

@Aaronaught - 它应该可以与解密一起正常工作（我有一些在SQL Server 2005中表现非常好的巨大相关FOR XML）- 但我想给OP一个运行示例，而不是基于他的代码的不可测试示例。 - Cade Roux

Answer 2

这可能是一个更适合超级用户组（DBA）的问题

- Cody C

---

我也会在那里发布，但我相信SO是最好的地方。 - Russ

DBA的职责应该是服务器故障吧。 - MartW

Answer 3

考虑到信用卡号码的哈希非常好 - Visa / MasterCard 16位CC的最后一位是校验和值。您是否考虑过通过自己实现并行性来实现，例如让每个线程获取其中模数（4）= thread_id的CC号码？假设有n个CPU /核心/今天称之为什么，您不希望有超过4个（2 *核心）并行处理线程。

- Adam Musch

---

他并不是在寻求一般的并行化任务方法，他要求的是在SQL Server中实现特定的方法，因为你实际上无法创建“线程”。这根本没有回答问题。 - Aaronaught

不同的会话/作业/进程是否可以执行相同的存储过程，每个会话使用不同的“线程”参数以并行化工作负载？此外，我也在尝试学习。我怀疑最佳解决方案是取消嵌套游标，但我特别在寻找并行解决方案。 - Adam Musch

Answer 4

是的 - 将游标重写为基于集合的查询，SQL Server 优化器应根据底层数据的大小自动并行化（或不并行化）。没有需要进行“特殊”开发工作来使 SQL Server 使用并行处理，除了一些基本的最佳实践，如避免使用游标。它会自动决定是否可能在多个处理器上使用并行线程，以及是否有用进行这样的操作，然后可以在运行时为您拆分工作。

- onupdatecascade