Is there anything like Parallel CURSOR?

I am using CURSORs to do some row-by-row operation.

OPEN CURSOR_T

FETCH NEXT FROM ...

WHILE @@FETCH_STATUS = 0
BEGIN
      ... Block begins

      ... Block Inserts results into a table

      ... Block terminates
END

I was wondering why this cannot be executed in parallel as the row operations are completely isolated and onl开发者_开发技巧y insert some rows into another table and each row has its own ID assigned so there is no obvious possibility of a conflict.

I was wondering if there is a way to parallelize this in pure SQL?

This is usually achieved through a queue: you select the 'to do' items and drop them into a queue, and at the same time queue readers (processing threads) are dequeuing the 'to do' items and process them one by one. There is a fine art in using tables as queues, the processing is often associated with activation and the enqueue/dequeue cycle is actually contiguous.

You could split the from statement in multiple parts, and run them in separate connections. For example, for the first connection, process all rows with an even id:

DECLARE cursor_t CURSOR FOR select id from books where id % 2 = 0

And for the second connection, all rows with an odd id:

DECLARE cursor_t CURSOR FOR select id from books where id % 2 = 1

You could also check if it's possible to eliminate the cursor. If you can rewrite the cursor using set-based operations, it will be much faster, and SQL Server can run it in parallel automatically.