CODEWITHSUNDEEP
sqlsql-servert-sql
Seb
Seb

Reputation: 977

Can you run a portion of a script in parallel, based off the results of a select statement?

I have a portion of code which, when simplified, looks something like this:

select @mainlooptableid = min(uid)
    from queueofids with (nolock)
while (@mainlooptableid is not null)
begin
    -- A large block of code that does several things depending on the nature of @mainlooptableid
    -- .
    -- .
    -- .
    -- End of this blocks main logic
    delete from queueofids where uid = @mainlooptableid
    select @mainlooptableid = min(uid)
        from queueofids with (nolock)
end

I would like to be able to run the segment of code that's inside the while loop parallel for all uids inside the queueofids table. Based on what happens inside the loop I can guarantee that they will not interfere with each other in any way if they were to run concurrently, so logically it seems perfectly safe for it to run like this. The real question is if there is any way to get sql to run a portion of code for all values in there?

NOTE: I did think about generating a temp table with a series of created sql statements stored as strings, where each one is identical except for the @mainlooptableid value. But even if I have this table of sql statements ready to execute, I'm not sure how I would get all of these statements to execute concurrently.

Upvotes: 0

Views: 112

Answers (3)

paparazzo
paparazzo

Reputation: 45096

Be aware that run in parallel will not necessarily make it faster if the threads are competing for the same resources.

I don't think SQL will parallelize statements. But SQL will parallelize execution within a single statement.

Most programming frameworks have parallel. For example in .NET this would rather straight forward. Create a procedure where you pass @mainlooptableid and just call it in parallel.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

No, there is no way to get SQL statements in the same script to run in parallel.

The closest thing to it is to try to create a set-based way of handling them, instead of running them in a loop.

Upvotes: 0

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

I can't think of a way to do this within a single SQL script; scripts are procedural. If you want to explore this idea, you'd probably need to involve some form of multi-threaded application which would handle the looping aspect, and open a thread to hand off the parallelized portion of your current script. Not impossible, but it does introduce some complexity.

If you want to do this all in SQL, then you'll have to rewrite the code to eliminate the loop. As noted in the comments above, SQL Server is set-based, which means that it handles a certain amount of parallelization by doing work "all at once" against a set.

Upvotes: 1

Related Questions