Multi-Threaded Database Queries

Question

So we have this ultimate fail vendor (it's a long story, but trust me here) that has created an application that has separate, but identical in design, databases that we need to query (10 of them actually). Each of these databases is for a different "location" - but it's all still information relevant to all locations.

I've written some code to start 10 unique threads to issue queries to each of the separate databases, wait for the results to finish, and then combine them into a single set of records that can be used an manipulated.

The question here is is there any risk for reading 10 separate databases, using the same credentials for each, all at once?

I anticipate it should be a non-issue since databases can have hundreds of connections at any given time, but I wanted to get some feedback before I committed to this design.

MS-SQL if you're curious

Answer 1

Assuming the databases are independent, the biggest risk I can see is that you will get views of a different point in time for each database. This can either be a non issue, or you can solve it by restricting the result set based on a temporal criteria.

Having a single process open 10 database connections and query them presents no risk by itself.

Another option, a lot more complex, would be to set up replication with a single master and the 10 DBs as slaves. This is database dependent and may or may not be feasible/reasonable.

Answer 2

You could also consider setting up linked servers and doing the querying in one big union all statement. I'd test both ways though to see which is more efficient. Plus with the linked servers the whole thing will fail if one of the servers is down for any reason. Depending on your needs, this may or may not be a plus.

edited to add If they are all on the same server, you don't need to do this, just run one query union all query that refernces each database.

Answer 3

It could be simpler to let the server do all the legwork for you, like this:

SELECT col1, col2 FROM Database1.dbo.SomeTable WHERE <some conditions here>
UNION ALL
SELECT col1, col2 FROM Database2.dbo.SomeTable WHERE <some conditions here>

(snip)

UNION ALL
SELECT col1, col2 FROM Database10.dbo.SomeTable WHERE <some conditions here>

No multiple threads, much simpler. This should only be used if all your databases are on the same instance

Answer 4

As a one time operation you can run 10 queries in 10 threads (as you do), you can run 10 queries w/o explicit threads using the async BeginExecuteReader methods, you can aggregate the query into a single one via UNION ALL (as Alex suggests) if all DBs are local, you can even play with the undocumented but well known sp_msforeachdb system procedure. The one think I'd recommend against is, if DBs are on separate, the use of linked servers. Linked servers induce availability dependencies that can hurt during operations (ie. one link is down, your reporting is down).

If this is just one of the many times you have and will have to do this, for many more queries and reports, perhaps is worth considering aggregating the data for reporting, via replication for instance, into one single database.

As a side note, not knowing other problems with the said vendor, the practice of separating 'business units' on a separate database is quite common, and in the balance of development effort (=price) and common application usage is often the right practice.