title: Can I spread out a long running stored proc accross multiple CPU's?
body: <p>[Also on SuperUser - <a href="https://superuser.com/questions/116600/can-i-spead-out-a-long-running-stored-proc-accross-multiple-cpus">https://superuser.com/questions/116600/can-i-spead-out-a-long-running-stored-proc-accross-multiple-cpus</a>]</p>
  
  <p>I have a stored procedure in SQL server the gets, and decrypts a block of data. ( Credit cards in this case. )</p>
  
  <p>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 )</p>
  
  <p>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.</p>
  
  <p>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 )</p>
  
  <p>My stored proc:</p>
  
  <pre><code>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
  </code></pre>