[Also on SuperUser - http://superuser.com/questions/116600/can-i-spead-out-a-long-running-stored-proc-accross-multiple-cpus]

I have a stored procedure in SQL server the gets, and decrypts a block of data. ( Credit cards in this case. )

Most of the time, the performance is tolerable, but there are a couple customers where the process is painfully slow, taking literally 1 minute to complete. ( Well, 59377ms to return from SQL Server to be exact, but it can vary by a few hundred ms based on load )

When I watch the process, I see that SQL is only using a single proc to perform the whole process, and typically only proc 0.

Is there a way I can change my stored proc so that SQL can multi-thread the process? Is it even feasible to cheat and to break the calls in half, ( top 50%, bottom 50% ), and spread the load, as a gross hack? ( just spit-balling here )

My stored proc:

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