Running the same t-sql statement in parallel

Question

Running the same t-sql statement in parallel

DamianC

SSCrazy Eights

Points: 8023
More actions
May 24, 2022 at 5:00 pm

#4038368

Hi
I have a stored procedure that writes rows to tables where it repeatedly calls a SP and passes values to perform the write

It does this by first building up some table detail in a temp table - Such as table name, id plus some other bits
It then uses a while loop and loops through this temp table, calling an SP and passing some of the temp tables values each time
Could be anywhere between 100 and about 2000 calls to the SP
So, each call is made serially and each time it writes data to a different table

I'm just wondering if this could run faster if I can somehow make multiple calls in parallel
Is that possible?

I fell like it's either a restructure of the SP that's required or a fundamental change to the approach e.g. SSIS

Thanks
- Damian

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 1

What would be better would be to make it so that groups of rows in the temp table that will be written to the same tables are handled all at once. That would require a change to the stored proc. Since that stored proc may be used by other things, then a new stored proc would probably be in order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 2

An even easier way for the future might be to make an update-able partitioned view that would auto-magically evaluate the data from the temp table and auto-magically write it to the correct tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DamianC SSCrazy Eights Points: 8023 More actions · Answer 3

That's an interesting thought.

Thanks Jeff - I'll try out these approaches

- Damian

kaj SSCommitted Points: 1606 More actions · Answer 4

As an alternative this could possibly/probably be better handled by SSIS than what you describe as your current solution.

Instead of writing to a temp table, just pass the data to a Conditional Split component where you split on the table name and pass the data to different output connections. This will in effect make your write process parallel (batching is automatically included on output). Of course this will not be a fully dynamic solution, since you have to define table output connections for each possible output table in advance. But still, unless you need a fully dynamic number of tables, SSIS may be a viable solution.

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 5

kaj wrote:

As an alternative this could possibly/probably be better handled by SSIS than what you describe as your current solution.
Instead of writing to a temp table, just pass the data to a Conditional Split component where you split on the table name and pass the data to different output connections. This will in effect make your write process parallel (batching is automatically included on output). Of course this will not be a fully dynamic solution, since you have to define table output connections for each possible output table in advance. But still, unless you need a fully dynamic number of tables, SSIS may be a viable solution.

That would also work... it's kind of like the partitioned view except done graphically.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)