[另外在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