Can I distribute a long-running stored process across multiple processors?

Question

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

I have a stored procedure on an SQL server that receives and decrypts a data block. (Credit cards in this case.)

In most cases, performance is acceptable, but there are a couple of clients where the process is slow, literally 1 minute to complete. (Well, 59377ms to return from SQL Server, to be precise, but it can vary by several hundred ms based on load)

When I look at a process, I see that SQL uses only one proc to execute the whole process and usually only proc 0.

Is there a way to modify my stored proc so that SQL can process the process multithreadedly? Is it possible to fool and split calls in half (50%, lower 50%) and distribute the load like a rough hack? (just flattening here)

My saved 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

How to use FOR XML to concatenate in one 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 2

This may be the best question for the SuperUser Group (DBA)

Answer 3

Consider your credit card numbers very good - the final CC number for Visa / MasterCard 16 is the checksum. Do you think you think your own parallelism is that each thread captures these CC numbers, where modulo (4) = thread_id? Assuming n CPUs / cores / no matter what they call today, you don't need more than 4 (2 * cores) parallel processing threads.

Answer 4

Yes. Rewrite the cursors as a set of queries and the SQL Server optimizer should automatically parallelize (or not) depending on the size of the underlying data. Using SQL Server parallelism does not require "special" work with developers, with the exception of some basic best practices, such as avoiding cursors. It will automatically determine if parallel threads can be used for several processes, and if it is useful for this, then it can split the work for you at runtime.