Q:

Multi-Threaded Database Queries

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

A:

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.

A:

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

