Can I have concurrent SQL selects in a stored procedure?

Question

Is there any way to run several SELECT statements concurrently inside a Stored Procedure in SQL Server 2008 (parallel execution)?

If so, what steps must be followed?

Do you mean parallel execution - multiple threads? – Kirk Broadhurst May 12 '11 at 01:29 — Kirk Broadhurst, May 12 '11 at 01:29
Exactly, something to make the execution concurrent – edgarmtze May 12 '11 at 01:49 — edgarmtze, May 12 '11 at 01:49

score 2 · Answer 1 · answered May 12 '11 at 01:35

2

If you mean can you control it, the answer is no.

However, if you have multiple selects where the results of those selects don't affect each other then the query optimiser will normally parallelise them for you, with provisios on cpu ability.

You can see this when you view the query plan;

Parallel Query Plan

but you won't see this in Sql Express, or any of the developer editions, only on full server editions.

answered May 12 '11 at 01:35

blowdart

55,577
12
114
149

So, there is nothing like create a thread which executes a `SELECT`... right? – edgarmtze May 12 '11 at 01:48

score 1 · Answer 2 · answered May 12 '11 at 04:59

1

There are no steps you can take:

multiple DML statements in one batch do not run concurrently
you can not switch this on
parallelism will be chosen by the optimiser based on query, indexes, cost etc: you can't switch it on

If you have 50 SELECTs I'd suggest you're doing something wrong. Why don't you ask about your real problem: instead of what you perceive the solution to your problem to be...

answered May 12 '11 at 04:59

gbn

422,506
82
585
676

I need to insert to different tables, that is the reason I have several select, Or is it a way to add a switch statement inside a select? – edgarmtze May 12 '11 at 13:47

score 0 · Answer 3 · answered May 12 '11 at 01:28

0

Not very clear your scenario, maybe you could use 'UNION' on several SELECT. or Temp tables to keep the intermidiate results.

answered May 12 '11 at 01:28

mothee

141
2
4
12

score 0 · Accepted Answer · edited May 23 '17 at 12:04

0

Agree with @blowdart completely - SQL will normally optimise the degree of parallelism for you.

The only thing that you can do is limit that degree of parallelism, with the MAXDOP option (MAXimum Degree Of Parallelism).

SELECT [Id] FROM [Table] OPTION (MAXDOP 1)

See also:

http://support.microsoft.com/kb/329204

What is the purpose for using OPTION(MAXDOP 1) in SQL Server?

edited May 23 '17 at 12:04

Community

1
1

answered May 12 '11 at 04:28

Kirk Broadhurst

27,836
16
104
169

Excuse, so if I have 50 Selects I add this code on every of them, And depending the cores I get a faster execution?. – edgarmtze May 12 '11 at 04:39

score 0 · Answer 5 · answered Jul 15 '11 at 19:24

You could use the service broker to run things in parallel. I have done this where I had to update or insert many rows into 15 different tables. I set the update/insert statements onto the service broker queue and let it run 10 way parallel.

There is some overhead in the queuing, but the inserts/updates are large enough - it can be beneficial.

Can I have concurrent SQL selects in a stored procedure?

5 Answers5