Menu
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 only 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?
711
Parallel cursor is the technique to increase the perforamance of the program. For example if we use nested select in our program instead of For all entries addition, then definetly performance going down. In the same way the if we use nested loops in the program it will also leads to down the performance.
Traditionally in ABAP, we use the LOOP using the WHERE clause for Nested loops. This type of nested loops are very common in our day-to-day programming. But, the cost, in terms of performance, is higher when we use the nested loops.
ABAP keyword CALL FUNCTION <function> STARTING NEW TASK <taskname> with the DESTINATION IN GROUP argument. Use this keyword to have the SAP system execute the function module call in parallel. Typically, you'll place this keyword in a loop in which you divide up the data that is to be processed into work packets.
An open cursor is linked to a multiple-line selection in the database table. To read the data into a target area in the ABAP program, use the following: FETCH NEXT CURSOR c INTO target. This writes one line of the selection into the target area target, and the cursor moves one line further in the selection set.
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.
193
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.
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
