Therefore we have this ultimate fail vendor (it is a lengthy story, but believe me here) which has produced a credit card applicatoin which has separate, but identical in design, databases that we have to query (10 of these really). All these databases is perfect for another "location" - but it is all still information highly relevant to all locations.
I have written some code to begin 10 unique threads to problem queries to each one of the separate databases, wait for a leads to finish, after which mix them right into a single group of records you can use an altered.
The question here's can there be any risk for reading through 10 separate databases, utilizing the same qualifications for every, all at one time?
I anticipate it ought to be a non-problem since databases might have 100s of connections at any time, however i desired to acquire some feedback before I devoted to this design.
MS-SQL if you are curious
Presuming the databases are independent, the greatest risk I can tell is you will get sights of the different time for every database. This could be either a non problem, or solve it by restricting the end result set with different temporal criteria.
Getting just one process open 10 database connections and query them presents no recourse alone.
An alternative choice, much more complex, is always to setup replication having a single master and also the 10 DBs as slaves. This really is database dependent and might be achievable/reasonable.
It may be better to allow the server do all of the legwork for you personally, such as 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, easier. This will simply be used if all of your databases are on a single instance
Like a once operation you are able to run 10 queries in 10 threads (while you do), you are able to run 10 queries w/o explicit threads while using async BeginExecuteReader techniques, you are able to aggregate the query into just a single one via UNION ALL (as Alex indicates) if all DBs are local, you may also have fun with the undocumented but well-known sp_msforeachdb system procedure. The main one think I'd recommend against is, if DBs take presctiption separate, using linked servers. Linked servers induce availability dependencies that may hurt throughout procedures (ie. one link is lower, your confirming is lower).
If the is among the numerous occasions you've and will need to do that, for a lot of more queries and reviews, possibly may be worth thinking about aggregating the information for confirming, via replication for example, into a single database.
Like a side note, being unsure of other issues using the stated vendor, the concept of separating 'business units' on the separate database is very common, as well as in the total amount of development effort (=cost) and customary application usage is frequently the best practice.
You might consider establishing linked servers and doing the querying in a single large union all statement. I'd test for both though to determine what is much more efficient. Plus using the linked servers the entire factor will fail if among the servers is lower unconditionally. Based on your requirements, this might be considered a plus.
edited to include If they're all on a single server, you don't have to do that, just run one query union all query that refernces each database.