Can I spread out a long running stored proc accross multiple CPU's?

Question

[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

Answer 1

This may be a better question for the SuperUser group (DBA's)

Answer 2

What about using FOR XML to do concatenation in a single correlated subquery:

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

Answer 3

Consider that credit card numbers hash very nicely -- the final digit in Visa / MasterCard 16 digit CC's is a checksum value. Have you considered roll-your-own parallelism by, for example, by having each thread grab those CC numbers where modulo(4) = thread_id? Assuming n CPUs/cores/whatever they're calling them today, you'd not want more than 4 (2*cores) parallel processing threads.

Answer 4

Yes - rewrite the cursors as a set-based query, and the SQL Server optimizer should automatically parallelize (or not) depending on the size of the underlying data. No "special" dev work is required to make SQL Server use parallelism, except some basic best practice like avoiding cursors. It'll decide automatically if it is possible to use parallel threads on multiple procs, and if it's useful to do so, and then it can split the work for you at run time.