Are there any performance implications of making cross database calls in SQL 2005?

Question

Are there any performance implications of making cross database calls in SQL 2005? In other words is it better for performance to have a stored procedure and function run within the same database as where the data resides?

Answer 1

If the databases reside in the same server instances, not so much. If they reside in different server instances you have the cost of transferring requests and data. So I guess the answer is yes, it is better for performance to have it run within the same database.

Answer 2

There's not a performance impact, but there can be disaster recovery implications. For example, if you're doing database mirroring, you might fail over one database to the DR datacenter before the other database fails over. Your queries would fail if they're designed to be executed in a different database than their own, and that database has failed over. For simplicity's sake, you would want to run them in the right database to prevent those kinds of issues - think about what happens years from now when you're gone and someone else has to maintain the app.

Second, there's a performance REPORTING implication. When you're doing profiles/traces of a database, you may try to filter activity based on the database. If queries are coming from a different database, you won't capture those unless you capture activity from both databases.

Answer 3

Answering your specific question, yes, the code is better in the same db as your tables.

• Ownership chaining (of code to tables)
• Cross db ownership chaining issues
• Managing security. Your server level login maps to a database level user. This would need maitianed twice
• BCP issues (version conflicts, mismatched data etc)

There is no performance overhead (same server) but there are huge maintainence issues.

Why are you thinking of this design, please?

Answer 4

gbn,

The database which contains the data is from a 3rd party MS Enterprise Server application. We did not want to place custom SQL within this database. We opted to create a custom database which does cross db queries where needed.