If I have the following RDBMS with the following databases and tables:
pets_database
dogs
cats
bunnies
wild_animals_database
bears
wolves
cheetahs
If I have a query that joins dogs and bears (which exist in different databases, is this less performant than if dogs and bears existed inside the same animals_database and the same query/join was executed? In other words, do joins across databases affect performance adversely, or do they have no effect? I'm using Sybase ASE 15 but assume it would be the same for most enterprise RDBMSes. Thanks in advance!
I would assume that there will be an impact on performance if only due to the fact that it's got to build seperate query plans for each database, read from different devices, different caches, etc. I would expect that the level of impact will largely depend on the size and complexity of the query.