Parallelism execution from a table

Question

Parallelism execution from a table

ypeng5

Mr or Mrs. 500

Points: 592
More actions
November 4, 2015 at 2:54 pm

#302012

I have a table with thousand of queries are waiting for execute in sql server. I am using an store procedure to execute those queries one by one. Is there any way that I can execute those queries in parallel to make the store procedure running more faster?

Viewing 4 posts - 1 through 3 (of 3 total)

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

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 1

A stored procedure is run by a session. You can have multiple sessions running the same stored procedure. Each session has a different spid (which is returned by SELECT @@SPID). You could create a ##global temporary table with the columns: QueryNumber, QueryCompletedDate, Spid. Your stored procedure could then check the ##table for queries that have completed, and execute a query number that has not yet been entered. However, using this approach will raise concurrency concerns and should be carefully tested (by parallel runs). You should carefully consider each session's transaction isolation level. A system with hundreds of sessions each running the same procedure will be very challenging to implement

The problem with the above approach is that it is reinventing the wheel: SQL Server was designed from the ground up to support running concurrent (i.e. parallel) tasks from concurrent sessions. The far better approach is to split up the stored procedure. Stored procedures are used to execute procedural code, and yet you are seeking to have its queries not follow one procedure

have2much SSChasing Mays Points: 640 More actions · Answer 2

what is the structure of the table which stores the query to be executed ?

Few questions

- Queries needs to be executed on different database ? If yes, then I may segregate and run at least one query on each database in parallel.

- Queries need to be executed on the same database object ? If yes, then I would like to regulate isolation level of the query to ensure that parallel run should not be just waiting for another query to finish.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

ypeng5 (11/4/2015)
I have a table with thousand of queries are waiting for execute in sql server. I am using an store procedure to execute those queries one by one. Is there any way that I can execute those queries in parallel to make the store procedure running more faster?

If SSIS is an option, SSIS would allow you to run stored procs in parallel.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001