0

Suppose I have 

INSERT INTO @tmp1 (ID) SELECT ID FROM Table1 WHERE Name = 'A'
INSERT INTO @tmp2 (ID) SELECT ID FROM Table2 WHERE Name = 'B'
SELECT ID FROM @tmp1 UNION ALL SELECT ID FROM @tmp3

I would like to run queries 1 & 2 in parallel, and then combine results after they are finished.

Is there a way to do this in pure T-SQL, or a way to check if it will do this automatically?

A background for those who wants it: I investigate a complex search where there're multiple conditions which are later combined (term OR (term2 AND term3) OR term4 AND item5=term5) and thus I investigate if it would be useful to execute those - largely unrelated - conditions in parallel, later combining resulting tables (and calculating ranks, weights, and so on).

E.g. should be several resultsets:

SELECT COUNT(*) @tmp1 union @tmp3
SELECT ID from (@tmp1 union @tmp2) WHERE ...
SELECT * from TABLE3 where ID IN (SELECT ID FROM @tmp1 union @tmp2)
SELECT * from TABLE4 where ID IN (SELECT ID FROM @tmp1 union @tmp2)
Improve this question
3
  • @Ioannis Karadimas: unrelated. Parallelism is one query having multiple threads. Not the same session having parallel queries Commented Nov 23, 2011 at 12:57
  • 1
    You are correct. I have removed the comment. Commented Nov 23, 2011 at 12:59
  • 1
    -1 for dumbed down question. See comments below Commented Nov 23, 2011 at 13:06

3 Answers 3

Reset to default
3

You don't. SQL doesn't work like that: it isn't procedural. It leads to race conditions and data issues because of other connections

Table variables are also scoped to the batch and connection so you can't share results over 2 connections in case you're wondering.

In any case, all you need is this, unless you gave us an bad example:

SELECT ID FROM Table1 WHERE Name = 'A'
UNION
SELECT ID FROM Table2 WHERE Name = 'B'

I suspect you're thinking of "run in parallel" because of this procedural thinking. What is your actual desired problem and goal?

Note: table variables do not allow parallel operations: Can queries that read table variables generate parallel exection plans in SQL Server 2008?

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The OP's querying two different tables -- not the same table: this won't work.
It's Table1 and Table2, and the example is just to explain that two selects are not related. My actual problem is that I investigate all possible solutions to a complex search.
@queen3: well, give us the actual problem and code then. Don't dumbing down a question and then complain/downvote about answers to the dumbed down question
I do not complain and do not downvote anything, actually I don't care about that at all. All I need is to know the possibility to parallelize queries or see if that's what SQL can do with hints or check in ex. plans. I do have 10 different ways to experiment with already, but there I know everything to make a decision. The only thing I don't know is if it's possible to... see above.
2

You don't decide what to parallelise - SQL Server's optimizer does. And the largest unit of work that the optimizer will work with is a single statement - so, you find a way to express your query as a single statement, and then rely on SQL Server to do its job, which it will usually do quite well.

If, having constructed your query, the performance isn't acceptable, then you can look at applying hints or forcing certain plans to be used. A lot of people break their queries into multiple statements, either believing that they can do a better job than SQL Server, or because it's how they "naturally" think of the task at hand. Both are "wrong" (for certain values of wrong), but if there's a natural breakdown, you may be able to replicate it using Common Table Expressions - these would allow you to name each sub-part of the problem, and then combine them together, all as part of a single statement.

E.g.:

;WITH TabA AS (
     SELECT ID FROM Table1 WHERE Name = 'A'
), TabB AS (
     SELECT ID FROM Table2 WHERE Name = 'B'
)
SELECT ID FROM TabA UNION ALL SELECT ID FROM TabB

And this will allow the server to decide how best to resolve this query (e.g. deciding whether to store intermediate results in "temp" tables)

Seeing in one of your other comments you discussing about having to "work with" the intermediate results - this can still be done with CTEs (if it's not just a case of you failing to be able to express the "final" result as a single query), e.g.:

;WITH TabA AS (
   SELECT ID FROM Table1 WHERE Name = 'A'
), TabAWithCalcs AS (
   SELECT ID,(ID*5+6) as ModID from TabA
)
SELECT * FROM TabAWithCalcs
Improve this answer

2 Comments

good answer, you should join us in dba.se chat chat.stackexchange.com/rooms/179/the-heap
If #temp tables are used rather than cte's (must due to lack of materialization hint), then I don't believe SQL server will ever parallelize the creation of the two temp tables. SQL server is still mostly procedural outside the bounds of a single statement.
1

Why not just:

SELECT ID FROM Table1 WHERE Name = 'A'
UNION ALL
SELECT ID FROM Table2 WHERE Name = 'B'

then if SQL Server wants to run the two selects in parallel, it will do at its own violition.

Otherwise we need more context for what you're trying to achieve if this isn't practical.

Improve this answer

2 Comments

Because I may need to do things on individual results before merging them - like, calculate weights. All in all, I only need the answer to the parallelism thing.
We need more details then. But you can't parallel with SQL. IF you do want to parallel, then that's a function of the client code which would be written in (for example) C#. It would have to open two connections to the database on two different threads, and then the results would be managed within client business logic.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.