How to query multiple, unrelated tables in parallel in SQL Server?

Question

I am given a list of table names and would like to get the distinct values of a specific column from each of these tables. For example:

Input = TableA, TableD, TableX

Output =

Table Name   |    Value

TableA       |    1

TableA       |    2

TableD       |    1

TableX       |    3

Since these tables are unrelated, it would be great if I could query them in parallel to save some time. I think querying them in one large UNION statement will let the SQL Server engine automatically parallelize the query, but I'd like to avoid this. Some of these tables are large, and it's possible querying them might fail on occasion due to memory/resource issues. So if I did a UNION, one table failing will result in the entire query failing.

Is my only option to handle this on the application side? (i.e make parallel calls to the same sproc for each table in the input list)

Thanks for any help and please let me know if the goal is unclear.

If you don't want SQL Server to do the parallelization, then yeah, I would say application side is your only option. — Blorgbeard
– Blorgbeard, Commented Mar 11, 2016 at 0:40
Got it, makes sense. Is there any other way for SQL Server to do the parallelization without requiring that all the queries succeed (i.e. via UNION)? — Corssica
– Corssica, Commented Mar 11, 2016 at 0:42
Not that I know of, but I am not that much of a SQL Server expert. It does seem like the simplest option is to handle it app-side, though. Although if one of these queries might fail because of memory issues, I don't know if running them all in parallel would be a great idea.. I'm sure you can do some performance testing though. — Blorgbeard
– Blorgbeard, Commented Mar 11, 2016 at 0:46
sorry i had an example posted of using UNION but i just saw you were trying to avoid this... w3schools.com/sql/sql_union.asp — John Lee
– John Lee, Commented Mar 11, 2016 at 0:50

Gordon Linoff · Accepted Answer · 2016-03-11 00:56:59Z

1

This is too long for a comment.

You are worried about SQL Server running out of memory but not your application? You seem to have a limited understanding of databases and resources. Or, you have a very strange environment, with very few resources on the database server and lots of resources on the application side.

Technically, you seem to want:

select distinct 'tableA' as tablename, value from tableA union all
select distinct 'tableB' as tablename, value from tableB union all
select distinct 'tableC' as tablename, value from tableC . . .;

It is not possible for different subqueries to have conflicting rows, because the table name distinguishes among them. Hence, don't incur the overhead of duplicate removal by using union.

This version of the query can also take advantage of indexes . . . on tableA(value), and so on. That might be very convenient.

As far as I know, SQL Server does not run union all queries in parallel. This is unfortunate. So, it is possible that a multi-threaded application that sends each query independently to the database would have better performance. However, constructing such an application can be tricky, when you have to wait for all threads to complete in order to combine results.

answered Mar 11, 2016 at 0:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Corssica Over a year ago

Thanks! Sorry if I'm misunderstanding, but for your first point, why would I worry about the application's memory? I just meant having the application make multiple connections to the database in parallel and running individual "select distinct" queries. The database would still be handling the requests and using resources. But it's interesting that "union all" doesn't run the queries in parallel. If I go with the app side approach, I could just have the individual queries insert into a final table, so no need to worry about order of combining results, right?

Gordon Linoff Over a year ago

@Corssica . . . I would start with a single union all query. If you need to get better performance, then you can test a multi-threaded approach. Multiple threads inserting to the same table also has performance implications.

Collectives™ on Stack Overflow

How to query multiple, unrelated tables in parallel in SQL Server?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related